Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Inexact match in Table Relationship

Inexact match in Table Relationship

Inexact match in Table Relationship

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?

V Boswell

RE: Inexact match in Table Relationship

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)

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

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

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close