I have a database with a table that collects data as I enter it from a form. I have a couple of queries that sort the data into two different reports. Here's some of what I'm doing. I'm collecting data associated to products. I have about 22 products and about 30 columns tracking problems with these products. I am counting the problems and sending them to the two different reports. Over the course or time I've collected about 2000 records associated with these products, this number will continue to grow as I enter daily data. In order to count all of these problems I've used "Sum" and different "expressions" to arrive at total counts for the problems. Everything works well. When I look at my report it shows the 22 different products and totals for all the defects associated with these products. Here's where I'm stuck.
I want to be able to query this data according to date, from the beginning of one month to the end of that month for example. The problem is when I do this my report shows every record for that time period, which may be a couple of hundred. I'm still looking for only the totals for each of the 22 products. Is there a way I can do this?
I hope I've explained my situation well enough to get help. If not let me know.
Thanks,
Don...........

Don-

This will be tough to answer without knowing the layout of your table and an example of the result you expect. If a product might have multiple records for a given time period and you want only one row per product, perhaps using a Totals query as the source of the report is the answer.


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

John,
     Thanks for your reply and interest. First let me tell you I'm not very knowledgeable about databases. Here's what I can tell you and hopefully it will make some sense.
I have a report that is built from one query. This one query is built from two other queries.
     The first Query is totals of all the defects IE, sum of cracks, sum of deep, sum of shallow, etc. Also in this query is the sum of all built. The way I arrived at this is by telling the query to "Sum" the columns.
     The second query is basically the same except only in this query I add the list of product that the first queries defects are associated with. I also use the "sum" feature in this query but I use the "group by" feature for the products. That way I get the 22 products listed. I then created another query that combines both of these into one. This one is the query I mentioned that I use to build the report.
     So I have all the information and entries for every day of this year with totals. What I need to do is pull entries only from certain dates, such as the beginning of the month to the end of the month, or any other time periods I may want to examine.
     The problem is when I ask the query to show me the records from the entire month I get many more records on my report than just the twenty two or so I want. This is because I have multiple entries for each day of the month even though I only have a total of 22 products.
     I'd like to have all this data and be able to query it for a time period and show only 22 records (one for each product) but also show all the defects totaled for each of these prodcuts. Sort of like having my cake and eating it too <g>.
     Anyway I hope that explains it a little better. Do you think there's any chance of eating my cake also?
Don.........

Don-

Open each of your queries in Design view, choose SQL View from from the View menu, and copy and paste the text you see into a reply here. I'll take a look at it and let you know what you need to do to add your filter.



John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

Here you go:
SELECT [Field Totals2(with boiler model)].[Boiler Model], [Field Totals2(with boiler model)].[SumOfTotal Tested], [Field Totals2(with boiler model)].[SumOfBad Plugs], [Field Totals2(with boiler model)].[SumOfBad Nipples], [Field Totals2(with boiler model)].[SumOfMfg Misc], [Field Totals2(with boiler model)].[SumOfBad Threads], [Field Totals2(with boiler model)].[SumOfPress Cracks], [Field Totals2(with boiler model)].[SumOfTop Nipple Leaks], [Field Totals2(with boiler model)].[SumOfBottom Nipple Leaks], [Field Totals2(with boiler model)].[SumOfLancaster Damage], [Field Totals2(with boiler model)].[SumOfZanesville Damage], [Field Totals2(with boiler model)].[SumOfStress Crack], [Field Totals2(with boiler model)].[SumOfThin Metal], [Field Totals2(with boiler model)].SumOfChatter, [Field Totals2(with boiler model)].[SumOfPorous Iron], [Field Totals2(with boiler model)].[SumOfSand Holes], [Field Totals2(with boiler model)].[SumOfGas Holes], [Field Totals2(with boiler model)].[SumOfChaplet Leaks], [Field Totals2(with boiler model)].[SumOfBlock Assy Gaps], [Field Totals2(with boiler model)].[SumOfZanesville Misc], [Field Totals].[SumOfTotal Tested], [Field Totals].[SumOfBad Plugs], [Field Totals].[SumOfBad Nipples], [Field Totals].[SumOfMfg Misc], [Field Totals].[SumOfBad Threads], [Field Totals].[SumOfPress Cracks], [Field Totals].[SumOfTop Nipple Leaks], [Field Totals].[SumOfBottom Nipple Leaks], [Field Totals].[SumOfLancaster Damage], [Field Totals].[SumOfZanesville Damage], [Field Totals].[SumOfStress Crack], [Field Totals].[SumOfThin Metal], [Field Totals].SumOfChatter, [Field Totals].[SumOfPorous Iron], [Field Totals].[SumOfSand Holes], [Field Totals].[SumOfGas Holes], [Field Totals].[SumOfChaplet Leaks], [Field Totals].[SumOfBlock Assy Gaps], [Field Totals].[SumOfZanesville Misc], [Field Totals].[total Defects], [Field Totals].ZDefects, [Field Totals].LDefects, [Field Totals2(with boiler model)].ZDefects2, [Field Totals2(with boiler model)].LDefects2, [Field Totals2(with boiler model)].expr1, [Field Totals].[%], [Field Totals].bp, [Field Totals].bn, [Field Totals].mfg, [Field Totals].bthds, [Field Totals].pc, [Field Totals].tnl, [Field Totals].bnl, [Field Totals].landmge, [Field Totals].cht, [Field Totals].gaps
FROM [Field Totals], [Field Totals2(with boiler model)];

Next:
SELECT DISTINCTROW Sum([Shift Table].[Total Tested]) AS [SumOfTotal Tested], Sum([Shift Table].[Bad Plugs]) AS [SumOfBad Plugs], Sum([Shift Table].[Bad Nipples]) AS [SumOfBad Nipples], Sum([Shift Table].[Mfg Misc]) AS [SumOfMfg Misc], Sum([Shift Table].[Bad Threads]) AS [SumOfBad Threads], Sum([Shift Table].[Press Cracks]) AS [SumOfPress Cracks], Sum([Shift Table].[Top Nipple Leaks]) AS [SumOfTop Nipple Leaks], Sum([Shift Table].[Bottom Nipple Leaks]) AS [SumOfBottom Nipple Leaks], Sum([Shift Table].[Lancaster Damage]) AS [SumOfLancaster Damage], Sum([Shift Table].[Zanesville Damage]) AS [SumOfZanesville Damage], Sum([Shift Table].[Stress Crack]) AS [SumOfStress Crack], Sum([Shift Table].[Thin Metal]) AS [SumOfThin Metal], Sum([Shift Table].Chatter) AS SumOfChatter, Sum([Shift Table].[Porous Iron]) AS [SumOfPorous Iron], Sum([Shift Table].[Sand Holes]) AS [SumOfSand Holes], Sum([Shift Table].[Gas Holes]) AS [SumOfGas Holes], Sum([Shift Table].[Chaplet Leaks]) AS [SumOfChaplet Leaks], Sum([Shift Table].[Block Assy Gaps]) AS [SumOfBlock Assy Gaps], Sum([Shift Table].[Zanesville Misc]) AS [SumOfZanesville Misc], Sum([Bad Plugs]+[bad nipples]+[mfg misc]+[bad threads]+[press cracks]+[top nipple leaks]+[bottom nipple leaks]+[Zanesville damage]+[lancaster damage]+[stress crack]+[thin metal]+[chatter]+[porous iron]+[sand holes]+[gas holes]+[chaplet leaks]+[block assy gaps]+[Zanesville Misc]) AS [total Defects], Sum([Zanesville Damage]+[Stress Crack]+[Thin Metal]+[Porous Iron]+[Sand Holes]+[Gas Holes]+[Chaplet Leaks]+[Zanesville Misc]) AS ZDefects, Sum([Bad Nipples]+[Mfg Misc]+[Bad Threads]+[Press Cracks]+[Top Nipple Leaks]+[Bottom Nipple Leaks]+[Lancaster Damage]+[Chatter]+[Block Assy Gaps]) AS LDefects, [ldefects]/[sumoftotal tested] AS [%], [sumofbad plugs]/[sumoftotal tested] AS bp, [sumofbad nipples]/[sumoftotal tested] AS bn, [sumofmfg misc]/[sumoftotal tested] AS mfg, [sumofbad threads]/[sumoftotal tested] AS bthds, [sumofpress cracks]/[sumoftotal tested] AS pc, [sumoftop nipple leaks]/[sumoftotal tested] AS tnl, [sumofbottom nipple leaks]/[sumoftotal tested] AS bnl, [sumoflancaster damage]/[sumoftotal tested] AS landmge, [sumofchatter]/[sumoftotal tested] AS cht, [sumofblock assy gaps]/[sumoftotal tested] AS gaps
FROM [Shift Table];

Finally:
SELECT DISTINCTROW [Shift Table].[Boiler Model], Sum([Shift Table].[Total Tested]) AS [SumOfTotal Tested], Sum([Shift Table].[Bad Plugs]) AS [SumOfBad Plugs], Sum([Shift Table].[Bad Nipples]) AS [SumOfBad Nipples], Sum([Shift Table].[Mfg Misc]) AS [SumOfMfg Misc], Sum([Shift Table].[Bad Threads]) AS [SumOfBad Threads], Sum([Shift Table].[Press Cracks]) AS [SumOfPress Cracks], Sum([Shift Table].[Top Nipple Leaks]) AS [SumOfTop Nipple Leaks], Sum([Shift Table].[Bottom Nipple Leaks]) AS [SumOfBottom Nipple Leaks], Sum([Shift Table].[Lancaster Damage]) AS [SumOfLancaster Damage], Sum([Shift Table].[Zanesville Damage]) AS [SumOfZanesville Damage], Sum([Shift Table].[Stress Crack]) AS [SumOfStress Crack], Sum([Shift Table].[Thin Metal]) AS [SumOfThin Metal], Sum([Shift Table].Chatter) AS SumOfChatter, Sum([Shift Table].[Porous Iron]) AS [SumOfPorous Iron], Sum([Shift Table].[Sand Holes]) AS [SumOfSand Holes], Sum([Shift Table].[Gas Holes]) AS [SumOfGas Holes], Sum([Shift Table].[Chaplet Leaks]) AS [SumOfChaplet Leaks], Sum([Shift Table].[Block Assy Gaps]) AS [SumOfBlock Assy Gaps], Sum([Shift Table].[Zanesville Misc]) AS [SumOfZanesville Misc], Sum([Bad Plugs]+[bad nipples]+[mfg misc]+[bad threads]+[press cracks]+[top nipple leaks]+[bottom nipple leaks]+[Zanesville damage]+[lancaster damage]+[stress crack]+[thin metal]+[chatter]+[porous iron]+[sand holes]+[gas holes]+[chaplet leaks]+[block assy gaps]+[Zanesville Misc]) AS [total Defects], Sum([Zanesville Damage]+[Stress Crack]+[Thin Metal]+[Porous Iron]+[Sand Holes]+[Gas Holes]+[Chaplet Leaks]+[Zanesville Misc]) AS ZDefects2, Sum([Bad Nipples]+[Mfg Misc]+[Bad Threads]+[Press Cracks]+[Top Nipple Leaks]+[Bottom Nipple Leaks]+[Lancaster Damage]+[Chatter]+[Block Assy Gaps]) AS LDefects2, [ldefects2]/[sumoftotal tested] AS expr1
FROM [Shift Table]
GROUP BY [Shift Table].[Boiler Model];
Hope this helps.
All I really want to do is take the data from a specific time period and show one record for each product and the quantities and defects associated for that product.

Don-

The first query makes no sense. You're getting all the rows from [Field Totals] (is this the second query?) combined with all rows from [Field Totals2(with boiler model)] (the third query?). There's no relationship between the queries, so you're getting garbage. If you want to filter on a date range, then there must be some date/time field in the query to which you can apply a filter. Does [Shift Table] contain a date? If so, then perhaps you need a WHERE clause using a parameter to filter on the date(s) you want. The parameter could point to an open form containing the date range in unbound controls.


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

John,
Your probably right although my report shows all 22 products and associated quantities and defect totals. Here's how a person with my knowledge and experience got it to work.
The 2nd query listed above shows only the sum totals for all the quantities and defects. It doesn't have any products listed. So it's one line that shows the total quantities.
The 3rd query listed above shows all 22 products and quantities and defects but doesn't show a grand total. I have one line for each of the 22 products and with each product row are the quantities and defects associated with that product.
When I wanted to put this into a report I didn't (and still don't) know how to create a report using more than one query. So my way around this was to create a third query (the first one listed above) that combines queries two and three. Then I built my report from one query.
Probably only makes sense to a jumbled mind like mine but it does work.
Does that explain the madness behind the first query? One other thing, your suggestion of using a totals query, I think that's what I did but I made it into more than probably necessary.

Thanks for your interest, I wish I could tap your knowledge because I'm sure there's an easy way to do this but it's beyond me. One thing on my side is time. I'm only planning on rolling out this new database next year........if I'm lucky.
Don.....

John,
I should also add that during the past week, as I was attempting to solve this problem, I did learn how to make my report from only one query. I still haven't figured out how to put multiple queries or tables into one report and of course I haven't figured out my main problem, that of quering by date and still only having one record for each product.
Thanks again,
Don.......

Hi,

   Did you get this to work yet? Just out of curiousity, how many tables are in this database and set of queries, and what are their relationships?

Thanks,


mmcdonal

Right now I have one table, one query and two reports. One of the reports serves a different function than the problem I'm trying to solve. I use a form to collect the data into the table. The query is set up to "group by" for the products, this shows one line for each product. The rest of the query deals with quantities and is set up to "sum". The report shows this query and in the text box I've set each quantity to sum so I end up with a report that has 22 rows, one for each product. Each product shows all quantities associated with it. Kind of like this.

Product Qty Built Defect1 Defect2 Etc
ABC 100 1 1 2
CBA 10 2 1 0
Totals 110 3 2 2
Hope that explains it better.
Thanks for your interst,
Don.........