Updating table with information

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James T - 15 Dec 2005 00:26 GMT

I have a database with 3 tables:

1) ProductDetails - fields are:

Autonumber
ProductType
Cost

2) Products - fields are:

Autonumber
ProductCode
Sold (Yes/No field)

3) Sales Transactions:

Autonumber
ProductCode (lookup of Product table)
ProductType (lookup of Producttype table)
Quantity
Price

I have created a form that references the Sales Transaction table and allows
me enter in the sales tranactions i.e. client buying products.

The issue is - a particular product can only be sold once (thus the reason
for the SOLD field in the product table). I need some way to have the system
flag in the PRODUCT table against the product I have choosen in the form that
the item has been sold.

I am not sure how to do this seeing the form is referencing the Sales
TRansaction table not the Product table itself.

Any thoughts...help...greatly appreciated.

Reply to this Message

Squirrel - 15 Dec 2005 10:08 GMT

Hi James,

Your Sales Transactions table does not reference the buyer at all. Are you
tracking sales with no
reference to the buyer? If yes, then consider this simple way to set your
Sold flag to Yes.

Add a button to your form called "Update Sold flag". It will call a query
coded thusly:

"Update Products set Sold = True where ProductCode in
(select ProductCode from SalesTransactions)

If your tablename really has a blank in it then change it to
SalesTransactions.

Also - I'd put the ProductType in the Products table rather than in the
SalesTransactions table.

HTH. Linda

>I have a database with 3 tables:
>
[quoted text clipped - 33 lines]
>
> Any thoughts...help...greatly appreciated.

Reply to this Message