Managing multiple tables of data bases

Q

queenierules

I have 2 tabels that use some of the same information. example customer info
is required for the work docket data base... How do I use part of the data
base without using the look up? but I need to see the information. I thought
that if I just used the id in both places it would pull the information over
but it doesn't seem to be doing it? I think I am doing something wrong?
 
J

John Vinson

queenierules said:
I have 2 tabels that use some of the same information. example customer info
is required for the work docket data base... How do I use part of the data
base without using the look up? but I need to see the information. I thought
that if I just used the id in both places it would pull the information over
but it doesn't seem to be doing it? I think I am doing something wrong?
Click to expand...

Yes. You're missing the whole point of how relational databases work, in fact!

You should store information ONCE, and once only; you can then use a Query
linking the two tables to bring information together. Storing the same
information redundantly in two tables is neither necessary nor appropriate!

Often, it's convenient to use a Form (for the "one" side table in a
relationship) with a Subform (for the "many" side table) to see information
pulled together. On a Report you can either use a report with a subreport in
the same way, or (better) base the Report on a query joining the two tables,
and use the Report's Sorting and Grouping feature to display the data as you
wish.

John W. Vinson/MVP
 
Q

queenierules

Thank you but I am new at this and I am still unsure of how to do it...
I create an address table and then I create a docket table minus the
addresses.
How do I bring them together in a query? I have created a form but I can't
seem to get the address to come into it. See what I am creating maybe I am
creating it in the wrong place is a work docket that has customer name
address etc. from the addresses table but then from the work table it has all
the areas that need to be filled in related to that specific job at that
time. then I need to take that form and created another form for invoicing..
 
J

John Vinson

queenierules said:
Thank you but I am new at this and I am still unsure of how to do it...
I create an address table and then I create a docket table minus the
addresses.
How do I bring them together in a query?
Click to expand...

You need a field linking the tables. Since I have NO idea how your
information is structured or what is related to what, I can only guess; but
if (for example) a single Docket has multiple Addresses, you would need to
have a Docket table with a unique DocketNumber. This field is called the
"Primary Key", and you should use the key icon in table design view to
specify it as such. This might be a number or a text field which you assign
manually, or perhaps you could use an Autonumber (if you don't mind a
meaningless number which may have gaps in the number sequence).

The Addresses table would need to have a "Foreign Key" - a field for the
DocketID, of the same datatype as the Docket table's Primary Key. This would
NOT be the Address table's primary key - it's just a link to the Docket table.

You would then create a Query by adding both tables to the query design
grid, joining them by DocketID.

of course, if each Address has several Dockets, you'ld reverse the
instructions above!

John W. Vinson/MVP
 
Top