Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Linking 3 tables together by dates and primary key

Status
Not open for further replies.

FB1

Instructor
May 31, 2005
69
GB
-- HD_OBs (FK_oid is the main link this is the Patientid)
select
oid,
fk_oid,
DIDAT as HDDate,
haloc as location,
access_used

from p_anthropometry
order by didat desc;


-- HD_Prescription (FK_oid is the main link this is the Patientid)
select
oid,
fk_oid,
HWDATE,
HWOACC

from p_hdpresc
order by hwdate desc;

-- Timeline (FK_oid is the main link this is the Patientid)
SELECT oid,
fk_oid,
TXDATE,
TXMOD,
TXREAS,
TXSUPR,
TXSITE

FROM p_timeline
order by txdate desc

I have Three tables with the above fields

HD_Obs will be the main table that will have a between parameter on the date (will be pulling monthly)
I need the other tables to link to the hd_obs table but by date.

i.e yes it must go to the correct Patientid but in HD_obs the date here is the main date per row
DIDAT - FK_oid - haloc -HWDATE - HWOACC TXDATE- TXREAS
02/02/2012 - 12 -- hosp - 30/12/2011 Graft 14/01/2012- Active
01/01/2012 - 12 -- hosp - 12/12/2011 Fistula 01/01/2012 - Active

The dates don’t always correspond with each other, so I need to pull the nearest one below from each table

Your Help is most appreciated, I know it should links on the FK_OID then the date but please how do I write this.
Ralph
 
Doing multiple JOINS can be tricky to type outright and get right. I use Microsoft Access, build copies of my tables, and use their query design tool to help build joins more seamlessly. This helps saves time so you can focus on the more important stuff. There are very slight syntax differences between the two, but no an issue if you just want to know what the join code needs to be. Helps me every time.


Creator of - Game Reviews, Game Lists, and much more!
 
Due to the nature of the business, Access cannot be loaded onto the pc's
buy thank you any way.

any help in stucturing the query would be most helpful
ralph
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top