relating fields to records

[ruthcarlton]

I have 2 DBs, Dailysales and MerchItems.

DailySales is date-based and contains information and calculations that track sales of Merchandise at shows, each item is accounted for with fields such as shirt1, shirt2, shirt3, and the actual names are auto-entered.

MerchItems contains 1 record per Item.

Currently I have a relationship for every Item (shirt1=::ItemName, Shirt2=::ItemName). This solution works, but is becoming labor intensive and limiting. Is there a better solution for relating these DBs?

[IdealData]

You appear to be explicitly matching each individual shirt type with its own field...move up another level...

Allow the content of a field (say ShirtType) to contain all the necessary info to make it unique for the purposes of relationships, then you only need ONE RELATIONSHIP to suit all ShirtTypes.

HTH

[danjacoby]

You really need three files:

1 to track sales ("DailySales")

1 list of items ("MerchItems")

1 that you never actually see, that has all the actual sales records (call it "LineItems").

MerchItems contains a list of items that could be sold, including any details about each particular item. You only use it to edit the items, or add or delete a particular item.

DailySales is the record of all sales, with each record being one sale (of one or more items). It is related to LineItems through a key field that is unique in each record in DailySales. Items entered in the portal in DailySales will be individual records in LineItems.

LineItems can be used to create summary reports if, for instance, you want a list of all sales to a particular client, or a summary of all sales of a particular item.

HTH,

Dan