Best way to accomplish this query and display of data

[Autoeng]

Working of a db for another department and need some advice as to best way to accomplish this query and display of data.

Data collected...
Date...........WU....Series...Shipped
01/01/05...10001...D187......Yes
01/01/05...10002...D187......Yes
01/01/05...10003...D187.......No
01/01/05...10004...F001......Yes
01/02/05...10005...D187......Yes
01/02/05...10006...F001......Yes
01/02/05...10007...G004......Yes

I am collecting data for units shipped = Yes by day and total by month so results are...

Series...01/01/05..January
...........Daily Qty..Mth Qty
D187..........2...........3
F001..........1...........2
G004..........0...........1

Now what I would like to do is put start and stop date fields on a form with an query execute button that would fire both queries and return the results on the form but I can't seem to figure it out.

I tried to combine the queries to give me one set of results and no matter how I join the queries I wouldn't get the results I wanted. It would drop the series from any that the daily ship = 0 like...

Series...01/01/05..January
...........Daily Qty..Mth Qty
D187..........2...........3
F001..........1...........2
.................0...........1

I wanted to keep this simple because it isn't mine and I don't want to invest a lot of time in creating this simple little interface (just typing this is more time than I wanted to spend) so I really wasn't wanting to create a start and stop date entry form which would fire the queries to a report, blah, blah, blah. Just put it all on one form.

I'm sure I'm missing something simple but it isn't a subform because that won't let you display continuous. Anybody willing to propose a simple solution?

Thanks

 

[KenHigg]

So you want to have:

Data collected...
Date...........WU....Series...Shipped
01/01/05...10001...D187......Yes
01/01/05...10002...D187......Yes
01/01/05...10003...D187.......No
01/01/05...10004...F001......Yes
01/02/05...10005...D187......Yes
01/02/05...10006...F001......Yes
01/02/05...10007...G004......Yes

In the top section and in the bottom section you want a summary that looks like:

Series...01/01/05..January
...........Daily Qty..Mth Qty
D187..........2...........3
F001..........1...........2
G004..........0...........1

???
kh

 

[Autoeng]

Data collected only resides in a table. I don't have to display it. I only want to display the summary that it takes 2 queries to generate (or at least I think it can only be done by using 2 queries).

 

[KenHigg]

This is a quick shot, but can it be done with a crosstab query...

kh

 

[Autoeng]

That was my thought and I finally got it to return the daily counts for each series listing -1 = shipped, yes and 0 = shipped, no but how can I get it to sum the months? This is my first stab at a crosstab.

 

[KenHigg]

I think this is one of those deals where it may help if you put a couple hundred rows in a seperate db and post it...

???
kh

 

[Autoeng]

You're right Ken. Pretty self explainatory, I think.

 

[KenHigg]

So for a given date, you want to know the units for each type series and the then you need the total for the series for that month?

kh

 

[Autoeng]

For a given date I need to know how many of each series and that day's grand total (all series together) and for the same for the month (how many each series and grand total all series together). The form is currently set up for only a given day and not the month.

 

[Autoeng]

Hey Ken, you still around?

 

[KenHigg]

Hey Ken, you still around?

Yes, Sorry. Post back if you have a problem with Pat's recommendation...

Ken

 

[Autoeng]

Can't get it

No need for apologies Ken just wondering where you wandered off to. I tried Pat's solution but can't get it to work. Attached is the db with qryMonthly = Pat's q1 and qryDaily = Pat's q2. I can't get the 3rd qry to work so i can't even save my attempts at it. If you or Pat could take a look I would be very appreciative.

Thank you again,

 

[Autoeng]

Thank you Pat. I did change the names (q1/q2) to match the table fields and still came up in error. I am wanting to do a combination of counting and summing.

To try and clear up confusion about what I need I have done as much as I can in queries and uploaded the latest.

I want to report:

By single day by series - qryDailyBreakDown
By single day grand total - qryDailyGrandTotal
By date range by series - qryMonthlyBreakDown
By date range grand total - qryMonthlyTotal

To get the results on a form in continuous view I need all of this to be from one query - qryTotalAll. All of the queries report properly except qryTotalAll. Because there are 16 different series to report on that query lists each series 16 times.

How can I get qryTotalAll to be an accurate representation of the 4 queries above or how can I get the information from the 4 queries onto a continuous form?

By using a form I can have the daily date field and the range date start and stop fields to enter and get results on the same page.

Thanks

 

[Autoeng]

Thank you Pat. I took out my join because it wasn't working while trying to include the summary queries and didn't want it to confuse the matter even further. Now for a little form field math and I'll have my total fields!

Again, thank you so much.

 

[Autoeng]

Misunderstanding. I took out the left join I had in before posting the db but am using the left join you put in. I trust that leaving the join you added will solve the cartesion product issue?

Simple question to finish this up. I still need grand totals for each of the columns created by qryTotalAll. Can I do that in a form field with a formula?

Thank you again for your concern and followup.

 

[Autoeng]

...Simple question to finish this up. I still need grand totals for each of the columns created by qryTotalAll. Can I do that in a form field with a formula?...

Got it. I was trying to use a label instead of an unbound text box...