Query Troubles...

A

ashley k

I'm making a database to put everything from my at home business on computer,
instead of pencil and paper. I have several tables which include: customers,
cash receipts, orders, and products. These are all working fine and I have
even created forms to put info into them more easily. Here's my problem:
I'm trying to create a query that will show how much each customer owes. On
paper, it's simple, multiply the quantity by price of each product and then
subtract the amount already paid. It doesn't seem to want to work with
Access. Here's what I have:
([Count of Product ID]*[Price])-([Amount Recieved])
This should work...sounds good and all, but here is the error I get everytime:
"You tried to execute a query that does not include the specified
expression.....as part of an aggregate function. I just don't understand what
I'm doing wrong here! Any help would be greatly appreciated!
Ashley
 
S

Steve Schapel

Ashley,

It will be easier to answer your question with some more specific
details. If you go to the design view of the query, can you please
select SQL from the View menu, and then copy/paste the SQL view of the
query into your reply here? Thanks.
 
A

ashley k

SELECT Customers.[Last Name], Count(Orders.[Product ID #]) AS [Products
Ordered], [Products Ordered]*[Price] AS [Total Order Amount], [Total Order
Amount]-[Amount Recieved] AS [Amount Due]
FROM Products INNER JOIN ((Customers INNER JOIN Orders ON
Customers.[Customer ID] = Orders.[Customer ID]) INNER JOIN [Cash Receipts] ON
Orders.[Order ID] = [Cash Receipts].[Order ID]) ON Products.[Product ID #] =
Orders.[Product ID #]
GROUP BY Customers.[Last Name]
ORDER BY Customers.[Last Name];

Is this what you were wanting to see? Sorry, I'm still so new to Access that
I've never seen this before.

-Ashley

Steve Schapel said:
Ashley,

It will be easier to answer your question with some more specific
details. If you go to the design view of the query, can you please
select SQL from the View menu, and then copy/paste the SQL view of the
query into your reply here? Thanks.

--
Steve Schapel, Microsoft Access MVP

ashley said:
I'm making a database to put everything from my at home business on computer,
instead of pencil and paper. I have several tables which include: customers,
cash receipts, orders, and products. These are all working fine and I have
even created forms to put info into them more easily. Here's my problem:
I'm trying to create a query that will show how much each customer owes. On
paper, it's simple, multiply the quantity by price of each product and then
subtract the amount already paid. It doesn't seem to want to work with
Access. Here's what I have:
([Count of Product ID]*[Price])-([Amount Recieved])
This should work...sounds good and all, but here is the error I get everytime:
"You tried to execute a query that does not include the specified
expression.....as part of an aggregate function. I just don't understand what
I'm doing wrong here! Any help would be greatly appreciated!
Ashley
Click to expand...
Click to expand...
 
K

kabaka

I think the problem is with

......[Products Ordered]*[Price] AS [Total Order Amount], [Total Order
Amount]-[Amount Recieved] AS [Amount Due].....

You can't use your newly named field as part of an expression within the
same query. Change this to:

......[Products Ordered]*[Price] AS [Total Order Amount], ([Products
Ordered]*[Price])-[Amount Recieved] AS [Amount Due].....
 
S

Steve Schapel

Ashley,

Yes, that's exactly what I wanted to see :)

Well, I think there are possibly some problems with the design of your
tables, and hence with the queries. Looks like you're tracking the
Receipts according to the Order, is that right? So in the Orders table,
you've got a Product ID field (by the way, as an aside, it is not a good
idea to use a # as part of the name of a field). And in any given
order, a customer can purchase more than one product, obviously. And
can they purchase more than one unit of any given product? If so, do
you have a Quantity field? Or if the order includes 2 of Product A, it
is entered as 2 separate records? In your Query, you are using
Count(Orders.[Product ID #]) where this will just give you the number of
records in the table for the Customer, so normally would not be relevant
to the calculation of the order price... hope that makes sense. Unless
all products are the same price, of course! Ok, speaking of price, is
the Pricwe in the query coming from the Products table, or the Orders
table. This is a classic question with this type of database. If the
price of a product may change over time, you will need to put the
purchase price in the Orders table, as you need to capture the price as
it was at the time of the order. And then there is the Customer. You
are using the Last Name field as if it is unique, but I would not advise
to count on this. If you are doing a query that works stuff out for
each customer, you need a CustomerID field or some such. Ok, next
point... Is it possible that a customer could make more than one payment
against a given order? In that case, you will need a Sum function
somewhere in the works in order to use the total amount paid per order
in the calculation of the Amount Due.

So, an incomplete answer, I know, but hopefully point you in a useful
direction.

Working with a database is very different from working with a
spreadsheet. I reckon it will definitely be worth the effort to get it
going.
 
Top