Using data from multiple tables

[Guest]

How do I use data from two (or more) different tables to create a record to
be placed into a new/separate table? Also, can I add additional information
that will only be contained in the new table?

Dan

 

[Rick B]

Sounds like you need to investigate one-to-many and many-to-many
realtionships. In short, you would not use data from one table in another
table. You could create a relationship (link) to tie data from the various
tables together. This is the purpose of a relational database.

For example, you might have one table with customer data (including an
account number).

Then you might have another table with Products (including a stock number).

You might have still another table with invoice headers (which would contain
an invoice number and the customer account number.) If a customer has two
invoices created, they would have two records in this table. Each record
would contain the same customer number, but a different unique invoice
number. It might also have the invoice date, and the salesperson number who
created the invoice. (of course, there'd be another table that contained
all the valid salesperson numbers and more details about them such as name,
extension, commission rate, etc.

You could have still another table that had the invoice details. It would
include the invoice number, and the stock number ordered by the customer.
If they ordered two products, they'd have two entries in this table.

You might take a look at the Northwinds sample database that comes with
Access to get a better feel for how relationships work.

Post back specific details if you need specific help.

Rick B

 

[Guest]

Rick,

I think maybe I better get specific. I want to create a computerized
checkout procedure in my company's archives room (instead of using the pen
and paper on the clipboard version). The project information is already in a
database (project name, project number, and client name). What I want is for
a person to be able to call up a project (probably based on the project
number), then select if they are taking files, drawings, and/or
specifications, which department they are from (mechanical, electrical, or
civil), then finally enter their initials and the date. This will create a
"database" of who has project information checked out of the archives. The
only information that will stay constant is the project information and the
categories for information they are checking out and departments.

I hope you can understand what I want.

Dan

 

[John Vinson]

Rick,

I think maybe I better get specific. I want to create a computerized
checkout procedure in my company's archives room (instead of using the pen
and paper on the clipboard version). The project information is already in a
database (project name, project number, and client name). What I want is for
a person to be able to call up a project (probably based on the project
number), then select if they are taking files, drawings, and/or
specifications, which department they are from (mechanical, electrical, or
civil), then finally enter their initials and the date. This will create a
"database" of who has project information checked out of the archives. The
only information that will stay constant is the project information and the
categories for information they are checking out and departments.

So you already have a Table of Project information - a list of the
files, drawings, etc. pertaining to that project?

If so you should NOT copy that information to your checkout table. It
would be redundant, unwise, and unnecessary.

Instead have a Checkout table containing fields for the ProjectID,
ItemID (from the table of files, etc.); the EmployeeID (initials are
emphatically NOT unique or reliable identifiers); DepartmentID would
become unnecessary if you have an employee table indicating which
department each person belongs to; CheckoutDate.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps