how to relate tables

[pjkbao]

I have a table where client services are recorded. Many services are linked
to another table which lists checks used to purchase these various services,
such as food vouchers. With each check, we purchase hundreds of items and
number them to have some control over the inventory. I have a field in the
services table now where the inventory numbers are typed in and then manually
reconciled by running reports, etc. I want to have each check attached to
the inventory numbers used for that check and then attach those inventory
numbers to the service transaction recorded in the client services table by
selecting them from some kind of subform, I think.
Any guidance is appreciated.

 

[Michelle F]

Hi there.

If I am understanding this right, you have a client service (lets call it
ServiceID) and each service is linked to a method of purchase (Purchase ID),
all ID numbers being unique. This means that for each service there is one
method of payment. Then, for each service and payment combination, there are
many items that are purchased (stored as ItemID). If this is right, here is
what I would do.

Create a tblServices with:
ServiceID (unique identifier)
ServiceType (pulls from a ServiceType table)
ServiceDate
ServicePurchaseID (refers to Purchases Table)

Create a tblPurchases with:
PurchaseID (unique identifier)
PurchaseType (pulls from a PurchaseType table)
PurchaseInfo (could be a memo field for other information about the
purchasing method)

Create a one to one relationship between ServicePurchaseID and PurchaseID.
So for every service, than can only be one unique purchase method. Now, as I
have laid this out, the Purchase table is a little redundant since the
relationship is one to one. You could combine the two tables.

Also...

Create a tblItems
ItemID (unique identifier)
ItemType (pulls from an Item Type table)
ItemInfo (can be memo field for additional information regarding this item)

Now, create a joint table that links the PurchaseID (or ServicePurchaseID)
to the ItemID(s). To do this, you will create two columns and set them both
as the primary key. This means you cannot repeat the combination of
PurchaseID and ItemID (many to many relationship). This means any purchase
can have many items and each item can belong to many purchases.

However, if each item can only belong to one purchase you can omit the joint
table and just add a column in your tblItems for PurchaseID.

Hope this helps! If I am wrong, maybe you can explain the situation a
little clearer. What tables do you have, what are the fields and
relationships you already have?

Michelle

 

[pjkbao]

Here is more info that might help you help me. I think you'll get his easily
if I explain it better.
I have these tables now:

tblSvcs
SvcID (auto id - unique)
SvcDate
SvcType (from table of services)
Ck# (from table of checks)
Inventory#s (entered manually into a text field now)
etc.

tblChecks
CkID (autoid - unique)
Ck#
BeginningInv#
EndingInv#
etc.

We'll use each check many times until we use all of the items purchased with
that check and each item is numbered. The number(s) are typed into the
inventory field manually now in the service table. I want to be able to
select the inventory numbers for the service transaction. The service might
have no inventory number or might have up to 8 numbers. Each inventory
number and check number combination should be unique and only be able to used
once.

Thank you very much.
PK

 

[Michelle F]

Alright, how about this. It seems like you have one check with many items
purchased on it (beginning and ending inventory numbers) and you have a
service which may or may not use the items listed under that check (as many
as 8). Lets try a sample set up:

tblInventory (each inventory item is associated with a check)

InventoryID (PK) CkID (from tblCks list)
101 1
102 1
103 2
104 2
105 3

tblCks

CkID (autoid - unique) Ck#
1 201
2 202
3 203
4 204

tblSvcs (each service is associated with only one check)

SvcID (auto id - unique) SvcDate SvcType CkID (from table of checks)
1 1/1/2008 2 1
2 1/2/2008 3 1
3 1/3/2008 2 2
4 1/3/2008 3 3

tblSvcsInventory (combinations of SvcIDs and InventoryID)

SvcID InventoryID (primary key, cannot use inventory
twice)
1 101
2 102
3 103
3 104
4 105

Now as far as only selecting inventory associated with the check number for
each service, you should use a cascading combo box on your form to filter the
inventory records by the check number entered for that service. For instance,
for SvcID# 2, a list would appear for inventory items 101 and 102 which are
associated with check 201. However, since the inventory number is unique in
tblSvcsInventory, you would only be able to select inventory number 102 since
101 was used for SvcID# 1.

Does this work? Let me know!

Have a great day,
Michelle

 

[pjkbao]

I think this is almost exactly what I need. Let me just add one more part.
Each inventory number is used over when a new check is written. So CkID is
unique, but the InvID isn't unique by itself. Only the combination of the
check and inventory will be unique. I feel this is so close! Thank you!
Also, what would you suggest for populating the inventory/check# table?

 

[Michelle F]

So maybe you should create your inventory table something like this...

tblInventory

InventoryID (unique, autonumber) InventoryNumber (was called InvID in your
example) Chk#

1 101 201
2 102 201
3 101 202
4 102 202

Set up the above table so that both InventoryNumber and Ck# are PKs
(InventoryID acts as a unique identifier of the pairs). Setting both as PKs
will ensure there are no duplicate entries for the pair of Ck# and Inv#. As
a rule, I like to set the ___ID field as my unique identifier, so that is why
I chose to call the second field InvNumber because it may be repeating.

Then in your tblSvcsInventory you will call up all InvNumber for each Ck#
but be sure to STORE the InvID for that field since it is your unique
identifier and will ensure non repeating pairs.

I don't know the best way to populate the new tblSvcsInventory without just
manually plugging everything in. You can set up the combo boxes in your form
that will limit the values you can type in (cannot type in inventory for non
existing service). You might try exporting your existing inventory for
services table into excel and manipulating the data that way. Then you can
import it back into Access. But you will want to be sure that you are storing
the unique identifier for the inventory item and not a number that can be
repeated (i.e., 101 may be used many times but #1 or #2 are unique in the
example above). There may be code that you can use to help populate this
table in the future, but I don't know too much about that. Someone else may
have a better idea. Something where you type into a form the inventory
numbers and it automatically repeats the check number for each entry...

Have a good day!
Michelle