Excluding data

[Vic1980]

Hi,

I have a database that holds information on items (which have a unique reference) that are to be sold. The database generates a report once an item is paid to tell users which company to send an item too.
Sometimes the item is returned but the user will need to send the item to another company at a later date.

The problem i have is when an item is re-sold the report that is generated pulls the company information for the 1st sale. Is there a way to get Access to pull the company for the second sale only?



Thanks,

[orange]

Tell us more about your tables. Provide a few sample records to show us the issue.

Sounds like you have information about

Items
Sales
Companies
...

and we have no idea how your tables are related.

[Vic1980]

Tables:
Items Table – Item Reference, Location
Sales Table – Item Reference, Sale Date, Price, Sale Reference, Customer Name, Customer Details
Payments Table - Item Reference, Date Paid

In the query the Items table is linked to the Payments table which is linked to the Sales table via the Item Reference.

The logic behind this is once an item is updated as paid it will appear on the report, the report contains the Item Reference, Location, Customer Name, Customer Details, Sale Reference

Example of error item is sold and then returned:

Item Ref Sold to Customer Sale Reference Sale Date
A123 Smiths 123 3rd Jan 2012 - item is returned
A123 Williams 189 15^th Feb 2012

The item is sold to Smiths but is returned and later sold to Williams. Once the item has been paid for by Customer Williams, the report is generated but pulls the Customer Details for Smith instead of Williams. I need it to pull the data for the most recent sale.

Please let me know if you need any more information.

[orange]

Quick review suggests these tables, but need to know more about Returns, When do they occur? After a Sale? You should have a separate Customer Table in my view.

Tables:
Items Table – Item Reference, Item Name, Item Details, Location
Sales Table – Item Reference, Sale Date, Price, Sale Reference,
Payments Table - Payment Id, Sale Reference, Item Reference, Amount Paid, Date Paid
Customer Table - Customer Id, Customer Name, Customer Details
Returns Table - Return Ref,Return Details, Return Date, Customer Ref, Other info to identify item??

I have acc2003 if you want to post a copy of your MDB with confidential info removed.