Count Total Records - Return only Records Requested 1

[DarrenBoyer]

I'm trying to show how many total vehicles our customers have bought. I run into problems when I want to combine that information (field) with the regular query I've always used.
(This query helps record whether we've sent our recent customers a certain gift certificate.)

The query that returns our regular gift certificate canidates looks like this:

SELECT [Manual-Vehicles].TransactionDate, Customers.IndivLastName, Customers.IndivFirstName, Customers.SpouseFirstName, Customers.Household, Customers.SpouseSurname, Customers.StreetAddress, Customers.Locale, Customers.Province, Customers.PostalCode, [Manual-Vehicles].GiftCert, [Manual-Vehicles].EmployeeID
FROM Customers INNER JOIN [Manual-Vehicles] ON Customers.CustomerID = [Manual-Vehicles].CustomerID
WHERE((([Manual-Vehicles].TransactionDate)>=[Forms]![frmGiftCertInput]![txtStartDate]) AND (([Manual-Vehicles].GiftCert)<>Yes) AND (([Manual-Vehicles].InventorySource)=[Forms]![frmGiftCertInput]![cmbDepartment]) AND (([Manual-Vehicles].SaleCategory)<>"Fleet"))
ORDER BY [Manual-Vehicles].TransactionDate, Customers.IndivLastName, [Manual-Vehicles].InventorySource, [Manual-Vehicles].TransactionDate;

The query that counts the number of vehicles looks like this:

SELECT Count([Manual-Vehicles].CustomerID) AS CountOfCustomerID
FROM Customers INNER JOIN [Manual-Vehicles] ON Customers.CustomerID = [Manual-Vehicles].CustomerID
GROUP BY Customers.IndivLastName;

When trying to combine these two queries I get either a limit of the total vehicles bought to the dates in the 1st query or that the query can only return 1 record. If it would be easier to abbreviate the solution of write a generic one I should be able to get that working. Thanks,

 

[dhookom]

If all you want is the total number of unique customers, create a totals query of your second query like:
SELECT Count(*) As NumCustomers
FROM qtotSecondQuery;

Then add this one record query to your top query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DarrenBoyer]

Thanks for your input Duane. I've spent some time evaluating what you suggested and I don't think I worded the question properly.

The output should be in columns that look like this:
Customer Name, Customer Address, Recent Sale Date, etc, TOTAL OF ALL VEHICLES EVER BOUGHT.

This would then output in a data sheet format for all customers within the Sale Date Parameter. The problem I get is that when I try to use the HAVING clause to limit my output it also reduces the total vehicles bought to those within the HAVING clause parameters. So instead of a big customer showing all 183 vehicles we have a record of them buying it only shows 2. Various subquery haven't worked but that's probably because my skill with them is still growing.

The query above pulls from two normalized tables: Customers and Vehicles.

 

[PHV]

Something like this ?
SELECT M.TransactionDate, C.IndivLastName, C.IndivFirstName, C.SpouseFirstName, C.Household, C.SpouseSurname
, C.StreetAddress, C.Locale, C.Province, C.PostalCode, M.GiftCert, M.EmployeeID, T.CountOfVehicles
FROM (Customers AS C
INNER JOIN [Manual-Vehicles] AS M ON C.CustomerID = M.CustomerID)
INNER JOIN (SELECT CustomerID, Count(*) AS CountOfVehicles FROM [Manual-Vehicles] GROUP BY CustomerID
) AS T ON C.CustomerID = T.CustomerID
WHERE M.TransactionDate>=[Forms]![frmGiftCertInput]![txtStartDate]
AND M.InventorySource=[Forms]![frmGiftCertInput]![cmbDepartment]
AND M.SaleCategory<>'Fleet' AND M.GiftCert<>Yes
ORDER BY 1, 2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DarrenBoyer]

PHV - THAT DID IT!!!![\b]
Thank You Very Much!!!

I see that you created another JOIN with a subquery but is there a name to that technique? I'm wondering in case someone searches this thread or for a simliar solution. The solution you posted was not in any books I keep laying around or in any searches that I knew to try.

 

[PHV]

I call that technique an inline view but I'm not sure it's the official name ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886