Multiple unrelated tables

[Rob D.]

Hello.

I'd like to use access as a form of database input/storage. What I'd like
to do is create a form where I'd be able to select an ID from TableA and
another ID from TableB and input that data to TableC. There is no
relationship between A and B. Is this possible? If so, can someone point me
in the right direction?

Thanks!

 

[scubadiver]

Do you want to put IDs from table A and B into the same column in table C?

 

[Rob D.]

Actually, I want to keep them in the same row. To be a little more specific,
it's not just the ID numbers that I want exported to TableC, it's the whole
rows of both A and B.

example:

TableA:
code, dealer, address

TableB:
part, price

TableC:
code, dealer, address, part, price, date<---Extra manual input if possible.

 

[scubadiver]

If you say part and price are unrelated to code, dealer and address how do
you know which rows go together? If you are putting information from two
different tables together in another table then surely table A and B are
related.

I hope you don't mind me asking how experienced you are in database design?

 

[Rob D.]

I'd put them together by selecting them manually from two seperate combo
boxes. At least that's how it'd work in my head....

 

[Rob D.]

I dont have any experience with database design other than manually entering
data into excel.

The situation here is that I've got a list of 500+ registered
customers(TableA) in a spreadsheet and another list of 300 parts(TableB) in
another spreadsheet. I'd like to be able to use both tables as inputs to
keep track of new sales(TableC) in a "Form" format rather than manually type
everything. Hence, my wanting A+B=C(AB). I've seen a lot of help topics
mentioning relationships but I dont think A and B are related at all, at
least, until they get to C.

 

[Beetle]

Hi Rob

For starters, when you are using Access, you can't think in terms of Excel.
This is known as "commiting spreadsheet", which is a heinous crime in
the Access world <g>.

You are actually on the right track, at least in as much as you have realized
that you are going to need three tables. Where you're going wrong is
trying to store data like customer names and part numbers redundantly
in the third table. All you need in there is ID numbers as foreign keys
and perhaps some fields that are specifically related to a purchase,
such as purchase date.

Basically, what you have is known as a many-to-many relationship
between customers and parts (a customer can purchase one/many
parts and a part can be sold to one/many customers). An example
table structure would be like;

tblCustomers
**********
CustomerID (Primary Key)
LastName
FirstName
other fields related to customer

tblParts
******
PartID (PK)
PartNumber
PartDescription
other fields related to parts

tblPartSales (junction table to define the M:M relationship)
*********
CustomerID (Foreign Key to tblCustomers)
PartID (FK to tblParts)
PurchaseDate

Then you would, for example, create a main form/sub form where
the main form would be based on the Customers table and the subform
would be based on the junction table. In the subform you would have
unbound controls that would *display* the appropriate part information
based on PartID.

Here are some links where you can find some excellent tips and
tutorials on Access, and feel free to post back here when you have
more question. Good Luck

http://www.accessmvp.com/JConrad/accessjunkie.html

http://allenbrowne.com/tips.html

http://allenbrowne.com/casu-22.html

http://www.mvps.org/access/