Match 2 tables, find primary key and form into new table!

[gangel]

Hi people, will make this as easy as I can to explain.
I getting much better at access but still coming up with things I'm screwing the coding up for!!

I have a Query: [Scripts] (which is a list of patients and their prescriptions at a pharmacy)
I also have a [Patient] table that has PatientID information etc
PatientID, FirstName, LastName, Drug

I have also imported data from a DIFFERENT ([HNImport]) program via excel. Patient names are same but now in one field.
I'm trying to achieve 2 things:

• give the [HNImport] patients the Patient ID from [Scripts]
• Join [HNImport] and [Scripts] into ONE table called [Merged] which has the matching primary keys of the patients so I can do totals and stuff.

My current idea is create a Query based on [HNImport] called [QuHNImport]
It will have all the fields from the original table, plus a calculated field with a Dlookup function to find the patient ID from my original [Patients] table!!!

So my current function for this field is DLookUp("PersonID","Person","[PatientName] =" & [LastName] & " " & [FirstName])

However this isn't working at all... im getting errors and also it is asking me to put in [lastname] etc.. so it just doesn't run.
If someone can point me in the right direction that would be great!!!


What I was planning after this was to append the [HNImport] with these patient ID numbers then run a new query which created a new table combining [HNImport] and [Scripts] now both with matching Patient ID's which I can then sort and get some real data from!!!

Thanks all for reading and im looking forward to learning where im going wrong!!!


Gangel

[June7]

Names make very poor unique identifiers. Slightest difference in spelling and your data is screwed. And what if there are more than one Jane Smith?

Parameters for text type fields need apostrophe delimiters, dates use #, and numbers nothing. Correct syntax for DLookup:

DLookUp("PersonID", "Person", "[PatientName] ='" & [LastName] & " " & [FirstName] & "'")

I thought your table was named Patient? What is Person?

[gangel]

ya sorry it is Patient, sorry im working in the pharmacy while trying to get this done :P

I 100% don't like the idea that im using names but there is no option, the only benfit si that the actual data is originating from the same place

[RAW] -> my extrapolated data with patient ID fields
[RAW] -> HN Program -> More user input changing script records, but not patient name -> export to csv. This DOESNT have patient ID field, just exports names

Hence my current issue.

I have tried accessing the MDB of the HNProgram to get the data that was rather than using an export import system, however the connection comes up with something like wrong access version etc and I didn't want to screw around with it in case it stops the HNProgram from working at alL!

[gangel]

hmmm ok still not working, it is still asking me for LastName and FirstName

PIDCalc: DLookUp("PersonID","Person","[PatientName] ='" & [LastName] & " " & [FirstName] & "'")

Tables (double checked them!!)

[Person]
has {PersonID}, {FirstName}, {LastName}

[QuScripts]
{PatientName} - From HNImport
{PIDCalc} - calculation as above.

Where am I going wrong?

[gangel]

am I doing this the wrong way around?

Should it be more like
PIDCalc: DLookUp("PersonID","Person","'"&[LastName] & " " & [FirstName] & "'="&[PatientName]

...? As lastname and Firstname are in [Person] whereas the PatientName is in the query im writing the calculation in..?

[June7]

Yes, good point and I should have seen that.

PIDCalc: DLookUp("PersonID", "Person", "[LastName] & ' ' & [FirstName]='" & [PatientName] & "'")

[gangel]

awesome that works ! I kind of go there! lol

Now to make it make a new table and to join the 2 together!