Date Query problem - combine multiple rows of same date

PG1

Registered User.
Local time
Yesterday, 23:03
Joined
Jun 27, 2018
Messages
19
Hello all,


New member here and I need help.
I have two tables: Production and Disposition. Both tables have fields named date and Qty. I need to calculate first pass yield. Sounds simple enough until there are multiple entries for the same date. I've created a Query where I group production by date and have the criteria for example 6/26/2018. The issue is that I end up with a row for each production entry on 6/26/18 when I really need a total for that date. I have calculation in my query that is properly calculating FPY but its for each row of the same date entry in my table.
Here's some data:
Production table:
6/26/18 1000
6/26/18 5000


Disposition table
6/26/18 10


Calculation I'm expecting is 6000/6010. What I get in the query table view is one row of 1000/1010 and another row of 5000/5010.


Any ideas?
 
What's the SQL of the query? Are you sure your date values don't have a time component?
 
I'm also sorting by operation which in this case is 'weld'. Date data type in the tables are set to Date/Time with format as Short Date.

SQL: SELECT Production.Date, [Dispo Data Table].Operation, Production.PN, 100*([Production]![Qty]/([Production]![Qty]+[Dispo Data Table]![Qty])) AS FPY
FROM Production INNER JOIN [Dispo Data Table] ON Production.Date = [Dispo Data Table].Date
GROUP BY Production.Date, [Dispo Data Table].Operation, Production.PN, 100*([Production]![Qty]/([Production]![Qty]+[Dispo Data Table]![Qty]))
HAVING (((Production.Date)=#6/26/2018#) AND (([Dispo Data Table].Operation)="weld") AND ((Production.PN)="1028"));
 
You're going to to get unique instances of operation and PN, and you should be summing the values.
 
Great! we're making progress. Sum works on one field but not the other. Here's what happens: I have 4 entries in the production table for 6/26/18. Each entry is 1000. There is one entry in the dispo table for that day with a quantity of 9.Query set to sum gives me the correct total of 4000 for production but multiples the dispo value of 9 by 4 for each production entry giving a total of 36 in the query table.
I changed the query to have dispo qty = count and that gives me the correct number of 9 which is good until there is more than one dispo entry for the same date...
BTW, thanks for taking the time to help.


Edit: Uploaded screen shots of tables and queries
 

Attachments

  • Query1.jpg
    Query1.jpg
    96.3 KB · Views: 59
  • Query2.JPG
    Query2.JPG
    62.5 KB · Views: 65
  • production table.JPG
    production table.JPG
    89 KB · Views: 62
  • dispo table.jpg
    dispo table.jpg
    98.8 KB · Views: 60
Last edited:
I suspect it isn't appropriate to join those tables. Try a query that joins them without the grouping and see if the results look reasonable to you. I think you need to union them together and query against that, if they both represent "transactions".
 
Welcome!

Two things regarding your "Date" field,

First, using "Date" as a field name may cause issues as it is a reserve work. I would suggest changing it to reflect what date it is, such as "ProductionDate" or "MFGRDate". This not only avoids using a reserved word it also reminds you what date you are saving it in.

Second, format does not equate to what is actually saved in the field. Formatting to "Short Date" does not change the underlying data, just what you see. Date/Time fields are actually numbers where the integer is the "Date" portion and the decimal is the "Time". This means that you may THINK the field holds "1 Jan 18" but inside it really holds something like 45218.00369953 (as it is also saving a time shortly after midnight). This means that two "Dates" that look the same because of your format may not actually hold the same value.

I'd do as Paul suggests, understanding that two "Dates" may not line up if you are using NOW() to fill them.
 
The best way to collaborate on query issues is just by data. No explanations, no query patching, just a starting point and and ending point.

So, if you aren't making progress hammering your query into submission provide these 2 things to start fresh:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. expected results of A. Show me what data you expect to end up with when you feed your query the data from A.

Again, just data--starting and expected results.
 
Will do. Thanks. I'll work on it a bit and get back to you.
 
Thanks for the help. I can run a query and get correct results. I queried each table separately with summing the data that I needed and then built a third query that pulls the results and calculates. Might not be the most eloquent solution but it works. I do have issues (don't we all?) with comboboxes but will create another post in the correct area.
 

Users who are viewing this thread

Back
Top Bottom