Help with relations

[K]

Hello.
I have quite a problem with relations, imagine a table Clients and a
table Products. if i relate them one to one... i would have the table
Products with a list of products, price, quantity, date, and a yes/no
object called Buy(to return results on a query for reports) the
problem is with date...

how can i relate or create fields and make this work..the way i could
know when a certain product was bought, like keeping a history of
every purchase of every client...
I just cannot figure it out ... could anyone help..
Thanks.

 

[Guest]

You are describing a Many-To-Many join. You fix it by adding a third bridging
or linking table. For example an Assembly can have many Parts. Also a Part
can be used in many Assemblies. This is a classic M-M relationship and
relational databases work best with 1-M relationships.

This third bridging or linking table has foreign key fields for something
like the PartNo from the Parts table primary key and AssemblyID which matches
the Assembly table primary key. This combination could be the primary key for
the table or at least a unique index to prevent duplicates. You could also
have a field for PartCount as an Assembly could use dozens of the same screws
for example.

With this bridging or linking table, you can ask questions in both
directions such as “this assembly uses which parts†or “this part is used on
which assemblies†by joining the three tables together in a query.

 

[Guest]

You are describing a Many-To-Many join. You fix it by adding a third bridging
or linking table. For example an Assembly can have many Parts. Also a Part
can be used in many Assemblies. This is a classic M-M relationship and
relational databases work best with 1-M relationships.

This third bridging or linking table has foreign key fields for something
like the PartNo from the Parts table primary key and AssemblyID which matches
the Assembly table primary key. This combination could be the primary key for
the table or at least a unique index to prevent duplicates. You could also
have a field for PartCount as an Assembly could use dozens of the same screws
for example.

With this bridging or linking table, you can ask questions in both
directions such as “this assembly uses which parts†or “this part is used on
which assemblies†by joining the three tables together in a query.

 

[Guest]

K,

The classic order entry scenario is shown in the sample database Northwind
(Help, Sample Databases, Northwind Sample Database). It consists of the
following basic tables:

Clients (One side with Orders)
-----------------------------------------------
ClientID AutoNumber (Primary Key [PK])
ClientName Text
Address
City
State
Phone
....other client attributes

Products
-----------------------
ProductID AutoNumber (PK)
ProductName Text
UnitPrice Currency
....other product attributes

Orders (Many side with Clients, One side with OrderDetails)
----------------------------------------------------------------------
OrderID AutoNumber (PK)
ClientID Integer (Foreign Key [FK] to Clients)
OrderDate Date/Time
....other order attributes such as salesperson, payment method, etc.

OrderDetails
--------------------------
OrderDetailID AutoNumber (PK)
OrderID Integer (FK to Orders)
ProductID Integer (FK to Products)
Qty Integer (or Single if it's possible to buy partial units)
UnitPrice Currency
Discount Single

Note that the only fields duplicated from one table to another are PK-FK
pairs. The Orders table doesn't need the Client address, it needs only the
FK ClientID to give "Access" to all the needed Client fields via a query
linked on the PK-FK combination. There is one exception--the UnitPrice field
is duplicated because the OrderDetails table needs to capture the unit price
stored in Products *at the time of the order*.

Data entry is accomplished through a main form based on Orders and a
continuous subform based on OrderDetails, linked by the OrderID (set in the
LinkMasterFields and LinkChildFields properties of the subform).

A particularly convenient order review screen would show all orders of a
given client, along with all the detail for each order, however, Access
prevents a continuous subform from being embedded on another continuous
subform. One nifty workaround an MVP related to me is the following:

Base the main form on Clients to display a single customer and their
address, phone, main contact, etc. Embed a continuous subform based on
Orders, linked by the ClientID. As you move from one client record to the
next, their Orders detail will display.

Next, create an unbound textbox on the main form, called, e.g., txtOrderID.
Then embed a 2nd subform on the main form based on OrderDetails. This time,
instead of linking to a *field* from the main form recordsource, set the
LinkMasterFields property to the name of the unbound textbox, txtOrderID, and
the LinkChildFields property to OrderID. This link means that the 2nd
subform will display the detail records from whatever order is identified in
the unbound textbox. All that's left is to write the OrderID to the textbox
whenever you change focus from one order to another (using the 1st subform's
OnCurrent event), or change to a different customer (using the main form's
OnCurrent event):

Subform's OnCurrent event:
Me.Parent("txtOrderID") = Me![OrderID]

Main form's OnCurrent event:
Me![txtOrderID] = Me.MySubform.Form![OrderID]

It's a beautiful thing.

Sprinks