Table Structures!!

[rsmith64]

I have a 'very' simple database to construct, but I am 'very' confused on the best way to set it up! I am importing customer information from one dbf file and a seperate 'attn' field from another dbf file into Access, and linking them together with their customer id number. So, that leaves me with two tables.

What the user wants me to do is put these pieces of information together onto one table, and create two additional fields allowing him to add data along side the linked data that I just imported. Does this make sense?

So, my questions are....

(1) - How would you construct the tables, the relationships, etc. ?

My data is Customer Number, Company, Address, City, State, Zip, Order Date in one table, and Customer Number and Customer_PO (Attn) in another table. Then, the user would also like to have a ThankYouCheck (yes/no) field, and a Letter (yes/no) field in addition to this other data.

(2) - How can I put two linked tables together into one table, and then add 2 additional fields that I can edit into the table as well?

(3) - Is there a way to simply 'update' an existing table in Access rather than 'append' and paste data into a table that already exists?

As you can see, I've definately got my work cut out for me, although I have a feeling this isn't as complicated as my brain is making it out to be. I would greatly appreciate the help.

-Rob
AIM: RobUAH

 

[zambezibill]

Rob,

1) The 2 table should be linked on their common fields, in this case I presume [Company Name] (life will be a bit simpler if the spaces are left out of field names).

However, unless there is potentially going to be more than one Customer_PO (Attn) per customer, then this data should be included within the Customer table (usually no reason to have seperate tables where there is a one-to-one relationship.

2) You can open the table in design view and add additional fields. Then use an Update query to add the data. (see Access help for some Update query examples).

3) See 2). Append queries will add more records. Update queries will add or update data in current records.

WARNING! WARNING! Before you try any of the above, backup a copy of everything to a safe place. Update queries are particularly dangerous in that they will overwrite data and can not be automatically reversed. Once you have a copy, experiment away.

Cheers, Bill

 

[grnzbra]

What is Customer_PO. Is it something in which a customer could have several of them? Are the Thank you and the Letter related to the Customer_PO. (I'm thinking that customer_PO referrs to a customer purchase order). If the thank you referrs to a purchase, it might belong in the table with the PO.

How does the user intend to use this? Is he going to be rooting around in the new table? If there is a one to many relationship between the two tables, perhaps you could give him a query to play with.

The main question is, what is the nature of Customer_PO?

 

[rsmith64]

I'm sorry, I didn't specify... customer_po is actually simply the "attn" line, the 'first name' of the person receiving the letter.

All the user wants to do is to be able to sort this data into who will be receiving "thank you checks" , and who will be receiving "letters".... Then I am going to take this sorted data and mail merge it with a 'thank you check' & 'letter' document that I have created in Word.

I know, it is a pretty basic function, but I can't seem to quite get the basics down, the 'update' query for example.

(I'm still grinding away.... )