Best way to accomplish this query and display of data (1 Viewer)

[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?

 

[Pat Hartman]

I would create two totals query and then join them with a left join. Make the "monthly" query the left side so that you will include all series that were shipped that month even if none were shiped on the particular day.

Query1:
Select Series, Format(ShipDate, "yyyymm") As ShipMonth, Sum(qty) as MonthlyQty
From YourTable
Where Year(ShipDate) = Year(Forms!YourForm!ShipDate) and Month(ShipDate) = Month(Forms!YourForm!ShipDate)
Group by Series, Format(ShipDate, "yyyymm");
Query2:
Select Series, ShipDate, Sum(qty) as DailyQty
From YourTable
Where ShipDate = Forms!YourForm!ShipDate
Group by Series, ShipDate;
Query3:
Select q1.Series, q2.ShipDate, q2.DailyQty, q1.MonthlyQty
From Query1 as q1 Left Join Query2 as q2 ON q1.Series = q2.Series;

 

[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,

 

[Pat Hartman]

You needed to change the field names to those in your table. Also, since you talked about quantity, I thought you were summing something. In actuality, it seems that you are counting something. So change the Sum(qty) to Count(*). I also missed the range part of your question and so the query looks for a specific date. Just change that to a range in both queries.

 

[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

 

[Pat Hartman]

SELECT qryMonthlyBreakDown.SERIES, qryDailyBreakDown.Cleared, qryDailyBreakDown.WIP, qryDailyBreakDown.DailyTotal, qryMonthlyBreakDown.Cleared, qryMonthlyBreakDown.WIP, qryMonthlyBreakDown.MonthlyTotal
FROM qryMonthlyBreakDown LEFT JOIN qryDailyBreakDown ON qryMonthlyBreakDown.SERIES = qryDailyBreakDown.SERIES
GROUP BY qryMonthlyBreakDown.SERIES, qryDailyBreakDown.Cleared, qryDailyBreakDown.WIP, qryDailyBreakDown.DailyTotal, qryMonthlyBreakDown.Cleared, qryMonthlyBreakDown.WIP, qryMonthlyBreakDown.MonthlyTotal;

1. I modified the source of the SERIES field. It needs to come from the monthly query rather than the daily query because there may not be a series entry for the day.
2. I created a left join - you had no join at all and so you were getting a cartesian product.
3. I removed the summary queries because they cannot be joined to the other tables since they have no fields in common. Once the left join is added (it is required), Access will not allow the other two queries to be joined with out proper joins defined.

I hope you understand that this recordset will not be updatable due to the aggregation.

You can add subforms to display the other two queries.

 

[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.

 

[Pat Hartman]

Leaving the cartesian product is a bad idea. They produce huge recordsets that Jet then has to wittle down. If you have just 1000 tables in each of the first two queries, that will result in 1,000,000 rows for Jet to deal with. So as your table size grows, the queries will get slower and slower.

You can get past the left join issue by creating an intermediate query that does the left join of the month and day queries. Then the final query can join that to the two totals. It's ok for that to be a cartesian product because two out of the three tables only have 1 row.

 

[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...