Grouping Dilemma...

[bluenoser337]

I'm reposting in this forum, as I think my other choice was wrong...and I'd like to explain more clearly... In Access 2000, I have a report where I group records of manufactured parts in the following way...LotNumber, Color, QC_Code, Length, Width, Depth. The report groups and sums all parts with the same values in all the above fields and that works fine, until I try getting data "since a certain date and time". In the report data "record source" I then have to add the date/time field to the SELECT...but I also have to add it to the "GROUP BY", and now the records are no longer grouped properly on the report. Instead of seeing...
10"x12"x14" Qty.2, I get...10"x12"x14" Qty.1 on the report twice. My exact code is [SELECT ProdTime, LotNumber, Color, QC, Length, Width, Depth FROM parts GROUP BY ProdTime, LotNumber, Color, QC, Length, Width, Depth]. How can I have the query look at the date but not use it in the grouping? Please note: The "since" date is feed to the report from VB6 in a DoCmd.OpenReport line [WhereCondition:="ProdTime > #" & starttime & "# "] Any assistance greatly appreciated!!!!






by date. DateTime is in the general date format. This works fine in my Access report. All records are grouped and counted properly. Now I need to query for a report for all records since a certain date AND time. The report no longer grouping properly, I believe because now I can't use the "int" in the query and that means every "DateTime" is different. How can I get around this? Working Query = SELECT Int(ProdTime) AS DateOnly, Color, Length, Width...GROUP BY Int(ProdTime), Color, Length, Width. Non Working Query = SELECT ProdTime, Color, Length, Width...GROUP BY ProdTime, Color, Length, Width. Many Thanks!!!!

 

[bluenoser337]

Ooops...ignore that second part of the previous post.

 

[PHV]

I then have to add the date/time field to the SELECT
In an aggregate query it is perfectly legal to have a where clause with conditions on fields not in the select list.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bluenoser337]

Thanks PHV...but if I don't have ProdTime in the select list, when the report opens, I am prompted for the ProdTime value from the WhereCondition. ?

 

[PHV]

Can't be the where clause in the query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

There is another posting with two possible solutions in your original thread. It would have saved lots of time and posting if we had known:
1) you were using VB6
2) you wanted to filter the record source in the DoCmd.OpenReport line.

Please try to explain all important constraints in your original postings.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[bluenoser337]

dhookom...thanks for your patience for someone "old enough to know better". I solved the problem by taking the grouping out of the report "Record Source" SQL. That's where my trouble was happening. Now I basically query everything in the required table with no grouping in the query...and let the report do the math (with the new header as you suggested). I admit that I didn't know how the whole table-query-report thing tied together. "Star" for you in the other thread.