Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear All,
I have 3 tables Customer, Items and Purchase.
Customer and Purchase are connected by ID. With what query do I record all
the changes that users make. Lets say... new purchase was made but it changes
the current item and does not record who had that item before.
Thanks a lot,
 
It is not, it was easier to keep a track to what did any of the customers buy.
I am looking for a simple solution how to do this database. If I have to
reorganize, I will.... I just need a guide to how to have a list of all
purchases done by a customer and how to do a transfer of purchases from one
client to another. It sound complicated I know...
 
It seems to me your table structure needs to be like this --
Customer --
CusID - autonumber - primary key
Name - text
Phone - text
Addr1 - text
Addr2 - text
City - text
ST - text
Zip - text
ZipPlus - text

Items --
ItemID - autonumber - primary key
Name - text
Size - text
Color - text
Price - number - single
etc -

Purchase --
CusID - number - integer - foreign key
ItemID - number - integer - foreign key
PurchDate - DateTime
QTY - number - integer
etc -

Customer and Item related to Purchase in a one-to-many relationship. Use a
joined query and form/subform for data entry. Form for Customer and in the
subform a listbox to select Items being purchased.

For every item puschased by the customer there is a new record made in the
Purchase table.
 
ok let me test it...
Thanks for the effort...

KARL DEWEY said:
It seems to me your table structure needs to be like this --
Customer --
CusID - autonumber - primary key
Name - text
Phone - text
Addr1 - text
Addr2 - text
City - text
ST - text
Zip - text
ZipPlus - text

Items --
ItemID - autonumber - primary key
Name - text
Size - text
Color - text
Price - number - single
etc -

Purchase --
CusID - number - integer - foreign key
ItemID - number - integer - foreign key
PurchDate - DateTime
QTY - number - integer
etc -

Customer and Item related to Purchase in a one-to-many relationship. Use a
joined query and form/subform for data entry. Form for Customer and in the
subform a listbox to select Items being purchased.

For every item puschased by the customer there is a new record made in the
Purchase table.
Click to expand...
 
Back
Top