duplicate records in a query/report

G

Guest

I have tables for purchases with fields
Supplier
Purchaser
Product
Quantity
Price

and a table for payments with fields
Supplier
Purchaser
Payment
Payment Date

I want to run a query and/or report to show Supplier, then
Purchaser(grouped), purchases (ie Price*Quantity Summed), and Payments, but
when I do the result is that every record in the purchases table is shown
once for every record in the payments table

example: a purchaser has bought items for £10 (one record in the purchase
table) and has made one payment of £10 (one record in the payments table). My
query returns two records each showing both the £10 purchase and the £10
payment, therefore giving totals of £20 for purchases and £20 for payments

Where am I going wrong?
 
G

Guest

Sorry "access user" - I didn't mention that the tables are related via a
third table called "purchaser" which has one field again called purchaser
which is the primary key
The purchaser field in the purchasertable has a one to many with the
purchaser field in the purchases table and also a one to many with the
purchaser field in the payments table

regards
 
G

Guest

Considering your description, and looking at the relationships, you may want
to re-think the design. If you're looking for a query/report that begins
with the Supplier, it may be wise to have a separate Supplier table.
(Although not absolutely necessary) you would relate this table to a Products
table. Your other tables could be redesigned to tie things together. Also,
the link between payments and purchases may not be so clear cut. I can make
many purchases on my credit card, but each month I only make one payment.
 
G

Guest

Agree with AccessTaxMan. Personally, I would have the following tables:

Supplier (unique)
Products (unique)
Customer (your 'purchaser') (unique)
Orders (unique)
Selling Prices (by product)
Receipts (by customer by order id)
Payments (by supplier id) - ie things you have purchased to sell to your
customers

Terminology is important to establish at the outset to avoid confusing
yourself as you develop the application. For example, 'purchaser' - you are
using this to mean customer.
Also, I think the key item linking most (all?) the tables will be product
not customer (purchaser) as it will appear in at least the first 5 tables
above. There may be sample databases in Microsoft's templates area for this
sort of application.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Table/query layout trouble 2
Complicated query 11
creating a report with Access 1
Query Problem 2
Display Report Total in Form 1
value on a specific date 3
How to query this 10
Duplicate issues across multiple columns 1

Top