Fixed Values in a Query

[Midland]

I am preparing a report listing production data for 30 work centers (numbers
4801, 5122, etc., for each day of a week (numbering them 1 to 5, right now.)
Each work center can have one to five jobs per day. My plan was to gather the
data by job in one query, gather it by day and work center in another, and
generate a report from the two queries.

Problem: how do I fix the number of lines in the daily query at 150, 30 work
centers on day 1, Monday, the same 30 on the second day, etc.

Fixing the number of fields in a query would seem the most elementary task
in the world, but for some reason, I cannot find a mechanism that allows me
to do this. I keep getting a result of about 36,000 records in the dynaset.

 

[KARL DEWEY]

It would help a tenie, tiny bit if you posted the queries.

 

[Midland]

Sorry, I don't follow you. Post them where?

 

[KARL DEWEY]

Post them here.
Open query in design view, click on VIEW - SQL View, highlight all, copy,
and paste in a post.

 

[Midland]

Possibly this will help: I have data of this sort:

Jobdate WC Seq Qty
1/3/09 4864 84 100
1/3/09 4865 3 60
1/3/09 4865 176 130
1/3/09 8254 0 0
1/3/09 8254 11 30
1/3/09 8254 4 60
1/3/09 8254 11 60
1/11/09 4861 40 135
1/11/09 4864 11 15
1/11/09 4864 60 60
1/11/09 4864 29 420
1/11/09 4865 110 190
1/13/09 4861 0 0
1/13/09 4861 37 400
1/13/09 4865 1 40
1/14/09 4861 67 270
1/14/09 4861 184 180
1/14/09 4865 50 285
1/14/09 4865 30 190
1/14/09 8254 26 80

.. . . and I want the query to sum everything by date and work center like
this:

Jobdate WC Seq Qty
1/3/09 4861 0 0
1/3/09 4864 84 100
1/3/09 4865 179 190
1/3/09 8254 26 150

1/11/09 4861 40 135
1/11/09 4864 71 75
1/11/09 4865 110 190
1/11/09 8254 0 0

1/13/09 4861 37 400
1/13/09 4864 0 0
1/13/09 4865 1 40
1/13/09 8254 0 0

1/14/09 4861 184 180
1/14/09 4864 0 0
1/14/09 4865 80 475
1/14/09 8254 26 80

Eventually, this winds up in a report for the days 1/1 through 1/14, but I
am putting it through a query so I can combine it with data sorted by other
categories.

 

[KARL DEWEY]

Use a totals query to build list of all WCs and JobDates --
WC_JobDate --
SELECT Jobdate, WC
FROM YourTable
GROUP BY Jobdate, WC;

SELECT WC_JobDate.Jobdate, WC_JobDate.WC, Sum([Seq]) AS SumOfSeq, Sum([Qty])
AS SumOfQty
FROM WC_JobDate LEFT JOIN YourTable ON WC_JobDate.Jobdate =
YourTable.Jobdate AND WC_JobDate.WC = YourTable.WC
GROUP BY WC_JobDate.Jobdate, WC_JobDate.WC;