Need help with queries.

RoyalYoshi

New member
Local time
Today, 12:25
Joined
May 21, 2012
Messages
3
Hi I'm fairly new to Access and would like a bit of assistance.

I have tables called tblCustomers, tblStock and tblOrders.
One of the fields need to be an OrderCount field, which is supposed to increase by 1 every time a customer orders.

For example, whenever Bob orders something for the first time, his OrderCount would be 1. The next time he orders something, his OrderCount would be 2 and so on.

How would I go about doing this in a query (using a query is my only option, unfortunately)?

Also, it would have to be done for different customers as well (e.g., Bob, Steven, Max, etc.) in order to calculate their counts.
 
Create a query that counts the number of orders for that customer by adding tblCustomers and tblOrders. Select the 'Totals' button on the menu and select 'Count' on OrderID in the Total: row of the grid, then save the query as a Select Query. Use this query as the table in another query with the 'Totals' button selected and 'Sum' in the Total: row of the grid. Use this query in Update query with the SumOf field as the UpdateTo row of the grid for OrderCount. That will do it. Then call the query every time the form unloads in VBA module for that form. If you need the code for that, just post that and I'll send it to you.
 
Clarkie, I'm going to be honest here and say that most of it made little sense to me (I am not trying to offend you; I apologise for being a dummy).
I'll try to better explain what I'm after.

So this is what my database looks like (nothing complicated):
tblCustomer
CustomerID [Primary Key, AutoNumber]
Firstname
Surname
Address
PhoneNumber
Email

tblStock
StockID [Primary Key, AutoNumber]
Name
Description
Cost

tblOrders
OrderID [Primary Key, AutoNumber]
CustomerID [Lookup with CustomerID from tblCustomers]
StockID [Lookup with StockID from tblStock]
OrderQuantity

Now back to the OrderCount thing.
Basically I'm looking for an expression/calculation that counts the number of times that a customer appears in tblOrders. The CustomerID will be used in the calculation.
But this calculation must be able to apply to all customers, which is the thing stumping me.
I was thinking along the lines of
OrderCount: Sum(IIF([CustomerID]=1290,1,0)) <-- 1290 is just an example
but that only applies for one customer, and not the rest.
Also, I'm only allowed to use Tables and Queries, so no Forms or Reports.
Is there a way or am I just asking for something that simply cannot be done?

... I hope that made sense.
 
Last edited:
Not sure if this will work.. so First create a Query as
Code:
SELECT [tblOrders].CustomerID, Count([tblOrders].CustomerID) AS OrderCount
FROM [tblOrders] LEFT JOIN tblCustomer ON [tblOrders].CustomerID = tblCustomer.CustomerID
GROUP BY [tblOrders].CustomerID;
Try what you get..
 
Code:
SELECT [tblOrders].CustomerID, Count([tblOrders].CustomerID) AS OrderCount
FROM [tblOrders] LEFT JOIN tblCustomer ON [tblOrders].CustomerID = tblCustomer.CustomerID
GROUP BY [tblOrders].CustomerID;

pr2-eugin, where do I put this in?
It's because I've only been taught to do queries via the Query Design view.
 
Exactly where you were taught to.. But in the SQL view of the design view.. The SQL view can be accessed at the bottom right corner of the Query Design window..
 

Users who are viewing this thread

Back
Top Bottom