query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a standard query, using it to perform calculations.

first column date
second column I would like a calculated field for specific part number
third column same as second only diff part number for same date

the only way I have been able to do this was to group by partnumber and
date, but it created two entries for each date. These value are being used
in a line graph along with other data on a daliy basis, the x axis is a
date/time axis.
Thanks in advance for any assistance.

here is SQL of what I have so far:
SELECT prod.DATE, Sum([Reject]+[SCRAP])/Sum([GROSS PARTS
PROD]-[SCRAP])*1000000 AS FTQ, Sum([Reject]+[SCRAP])/Sum([GROSS PARTS
PROD]-[SCRAP])*1000000 AS FTQ2, 250 AS [goal ftq]
FROM prod
WHERE (((prod.[PART NUMBER])="2304-be" Or (prod.[PART NUMBER])="2304-de"))
GROUP BY prod.DATE, 100
HAVING (((prod.DATE) Between DateAdd("m",-1,[Forms]![Run Date]![DATE]) And
DateAdd("m",1,[Forms]![Run Date]![DATE])) AND ((prod.[PART
NUMBER])=("2304-BE")))
ORDER BY prod.DATE;
 
UPDATED SQL: Best I have gotten, but need on single line per date instead of
multiple lines per date.


SELECT prod.[PART NUMBER], prod.DATE, IIf([PART
NUMBER]="2304-BE",Sum([Reject]+[SCRAP])/Sum([GROSS PARTS
PROD]-[SCRAP])*1000000,"") AS FTQ, IIf([PART
NUMBER]="2304-DE",Sum([Reject]+[SCRAP])/Sum([GROSS PARTS
PROD]-[SCRAP])*1000000,"") AS FTQ2, 250 AS [goal ftq]
FROM prod
WHERE (((prod.[PART NUMBER])="2304-BE" Or (prod.[PART NUMBER])="2304-DE"))
GROUP BY prod.[PART NUMBER], prod.DATE
HAVING (((prod.DATE) Between DateAdd("m",-1,[Forms]![Run Date]![DATE]) And
DateAdd("m",1,[Forms]![Run Date]![DATE])))
ORDER BY prod.[PART NUMBER], prod.DATE;
 
Back
Top