creating form with 3 tables, need data to be shared (filled in / defaulted)

[winanass]

ok, i am in the computer parts sales business.
some companies, such as IBM, have multiple part #'s for the same thing.
IE, retail part #, FRU (Field replacement unit), internal part #, etc.
I need to be able to trace all these part #s back to the main #.
So, I have been thinking on devising 3 tables:

Inventory Master
-----------------------
Retail Part #
Brand
Description

Inventory Xref
----------------------
Part #
Retail Part # (ie, cross references back to the master inventory)

Transaction (ie, this is where i would receive the inventory, keep
track of qty and cost etc)
-----------------
Trans ID
Part # (could be anything, it would link back to Inventory Xref, which
would link back to Inventory Master)
Condition (like new, used, etc)
Qty
Cost

So i create a form like this:

Trans ID .. Part # .. Retail Part # .. Brand .. Description ..
Condition .. Qty .. Cost


how do i make the form work so that the Inventory Xref file and the
Inventory Master file
records are created automatically, and where the data in these 2 files
are used when the
correct data already is in the file (ie, Inventory Xref record already
exists, and the Inventory Master record already exists, the Retail Part
#, Brand, and Description should automatically pull up.)

hope i have made myself clear. i am very new to access, but come from
a previous database programming background using the Pick O/S

Thanks in advance...

 

[Guest]

Pick - Wow, I haven't even heard anybody mention Pick in a gob of years.
Anyway, Just a general idea. Acces Forms have to be related to one and only
one record source. It can be a table or a query. Using a query allows you to
use data from more than one table; however, you have to be careful because
some queries are not updatable, for example a "Totals" query and the data
source for a form must be updatable.

If there is only a 1 to 1 relationship between the "real" part number and
any other part number, would it not be reasonalbe to just have a column for
each type? I know the anwer is probably not, for a number of reasons, So I
think the approach I would consider would be an Inventory table and 1 part
number xref table where it is 1 to many from Inventory to part number xref
with these possible fields:

XREF_ID - Auto number field and Primary Key
INV_ID - A Long Integer that would be used to relate to the Inventory table.
The value would be the Primary key of the row in the Inventory table this
record is realted to
PART_NO_TYPE - What kind of Part Number it is
PART_NO - The number itself.

 

[winanass]

not really sure if either i followed your example or, that you
understood my example file structure. I am asking 2 questions:

1) how do you "fill in" a field being input on a form with data that is
being supplied by another table (ie, not the table the form is based
on), (ie, similar question to a field being
a yes/no with the default always being "yes", except in this case the
data is coming from a secondary table),

2) is the file structure as i had laid out acceptable to ms access.
as for the file structure, based on temp data i have used, it works,
with the relationships and all.
but what throws it out of wack is when the data from one or both
secondary tables (the Inventory Master and the Inventory Xref) is blank
due to the records not existing.
ie, one option i have considered is having a command button on the form
(for each secondary table - the Inventory Master and the Inventory
Xref) to add the necessary records to the tables in order to be able
to do data entry in the primary form (the transaction file)

 

[Guest]

Question 1:
After reading your post in more detail, I don't think you need to get data
from a different table to your form. What you really need is a form with a
sub form. The main form would be bound to the inventory table and the sub
form to the transaction table. This is the mos common way to do that.
Question 2:
In the Transaction table, you say the part number could be anything, but
will use the xref to get back to the master. What are the possibilities that
two different part numbers for different parts could be the same?
What I would suggest is that your transaction table use the retail part
number rather than one of the other numbers. You could still use the xref
table to find the part. A combo box would be perfect for that lookup. make
it 2 columns with the other part number as the bound column. The combo would
be bound to the xref table. To keep the Xref table up to date, you could use
the Not In List event of the combo box to add new number for new parts.

So I would lay the tables out like this:

Inventory Master
-----------------------
Retail Part # - Primary Key - Relates to Inventory Xref
Brand
Description

Inventory Xref
----------------------
Part # - Primary Key
Retail Part # - Relates to Inventory Master

Transaction
----------------------
Trans ID - Autonumber - Primary Key
Retail Part # - Indexed - Relates to Inventory Xref
Condition (like new, used, etc)
Qty
Cost