Inexact match in Table Relationship

[vboswell]

Is it possible to define a relationship between tables that considers a match on a date field if the date field value falls "on or before" a date in the base table?

For example, a puchase database includes a Cost Center Code associated with a purchase. The Cost Center Description is found in a related table. However, from year to year, this Cost Center Code may become associated with another location. When a Cost Center Description changes, we add the Cost Center, the new Description, and the Change Date in the Cost Center Table.

I would like to define a relationship between the purchases table and the Cost Center table that would correctly show the Cost Center Description based on the Cost Center Code and the Date of Purchase.

The thing is, the "actual" Purchase Date may not be in the Cost Center Table, since we only add the date of any changes to the Cost Center Code/Description combination.

The relationship would have to be based on the Cost Center Code, then find the Cost Center Change Date that falls on or before the Purchase Date, as the Description would be valid for the date of the purchase if the purchase happened on or after a change was made to the Cost Center table.

Visually, then:

For Purchase table:

Purch Date Cost Center Cost Delivery Location
---------- ----------- ---- -----------------
02/21/85 4567 17.03 Room 314
11/30/90 8463 21.16 Trailer 1
08/12/96 4567 78.34 Admin
03/11/01 8125 37.95 ICU

And Cost Center Table:

Cost Center Change Date Department
----------- ----------- -----------------
4567 07/01/83 Immunization Clinic
4567 07/01/87 Admin Support
8463 07/01/83 Triage
4567 07/01/93 Standardization
8125 07/01/83 Intensive Care


I want a relationship that would combine these tables to show:

Purch Date Cost Ctr Cost Delivery Loc Department
---------- -------- ----- ------------ ----------
02/21/85 4567 17.03 Room 314 Immunization Clinic
11/30/90 8463 21.16 Trailer 1 Triage
08/12/96 4567 78.34 Admin Standardization
03/11/01 8125 37.95 ICU Intensive Care

Is there a way to do this?

Thanks,
V Boswell

 

[ghubbell]

Hello V Boswell,
My bet is you could with a bit of work run some queries that would produce your results. This would be a band-aid solution... There is a structural flaw in your database that could be corrected with something like this:

Location Table
LocationID (P-an)
Location

CostCenterTable
CostCenterID (P-an)
LocationID (F,1-n)
CostCenter (as you see it)
ChangeDate DV=Date

PurchaseTable
PurchaseID (P-an)
CostCenterID (F,1-n)
PurchaseDate DV=Date
Cost

P-an Primary, autonumber. F,1-n foreign key, one to many, DV default value.

This would allow multiple cost centers based off of their location. Results could be extracted by any method right back to the location.

Your existing data: would have to be stripped, based on date criteria in queries, then appended to the new tables.

In summary, instead of using the Cost Center Number to control the purchase, use the CostCenterID which is a result of the LocationID. This might appear to be a bit of work but in the long run would save you more than the initial investment.

I hope I have understood your request correctly and if you choose to modify your structure please let me know if I can be of any help.

Gord
ghubbell@total.net