Query Problems

  • Thread starter Thread starter Aly
  • Start date Start date
A

Aly

I'm running the following query:

SELECT [Records Database].IPT, [Records Database].[TEST
TYPE], [Defect Codes].[Defect Code], Count([Defect Codes].
[Defect Code]) AS [CountOfDefect Code], [Records
Database].DATE FROM [Defect Codes] INNER JOIN [Records
Database] ON [Defect Codes].Description = [Records
Database].[DEFECT CODE]GROUP BY [Records Database].IPT,
[Records Database].[TEST TYPE], [Defect Codes].[Defect
Code], [Records Database].DATE HAVING ((([Records
Database].DATE) Between [ENTER START DATE] And [ENTER END
DATE]));

I want this query to give me a total for each defect code
that occurs within a month for a given date range. Right
now it tells me the total for each day in the range. What
am I doing wrong? All help would be greatly appreciated!
 
I want this query to give me a total for each defect code
that occurs within a month for a given date range. Right
now it tells me the total for each day in the range. What
am I doing wrong?
Click to expand...

You're grouping by the date in the query, so it's doing what you ask
(instead of what you want - computers are pesky that way!)

Open the query in design view and change the GROUP BY under the Date
field (which you should consider renaming, by the way - Date is a
reserved word and Access will get confused!) to WHERE. The SQL would
be

SELECT [Records Database].IPT, [Records Database].[TEST
TYPE], [Defect Codes].[Defect Code], Count([Defect Codes].
[Defect Code]) AS [CountOfDefect Code], [Records
Database].DATE FROM [Defect Codes] INNER JOIN [Records
Database] ON [Defect Codes].Description = [Records
Database].[DEFECT CODE] GROUP BY [Records Database].IPT,
[Records Database].[TEST TYPE], [Defect Codes].[Defect
Code]
WHERE ((([Records Database].DATE) Between [ENTER START DATE] And
[ENTER END DATE]));
 
Back
Top