Comments below.
..
There are 2 ways to do this in VBA.
1. create the queries in your database and then just execute from within VBA
2. create the SQL string on the fly within VBA and then execute that SQL
string from within VBA.
The first is easier and lets you use all the nice interfaces like query
builder that MS Access provides. But you have to use the queries as they
are. The later requires slightly more coding in VBA, and requires you to
"hardcode" the SQL within your VBA (not 100% true, you can extract the SQL
string from your existing queries and continue from there), but it is more
flexible as you can adjust the SQL in millions of ways on the fly (for
example you could change the query to only return duplicates for people with
names starting with J simply because it is a Wednesday today and thats what
you do on Wednesdays).
Which is best depends on your needs, your personal database protocols,
methodologies and "style", and I suppose on your skill in some respects.
I will assume you will use method 1. Furthermore I will assume you know how
to create queries in a database, and how and where to put VBA code (eg
behind a button so that it is executed when the button is clicked). If you
dont know how to do these things then you are in "newbie" territory, ask and
the newsgroup community will assist.
Firstly create the queries in your database.
In your VBA routine (for example in the code behind a button on your form)
you will need to include the following somewhere. Where will depend on what
else is in this routine, ie there may be some other code before or after
this code.
You may well need to be doing additional things, for example if you use VBA
to create the duplicate table perhaps you will also want to pop up a msg box
informing the user that duplicates exist and then do something about them.
I have not dealt with any of that sort of stuff (and I cannot as I dont know
what you want to do!), all I have done is list the basic commands you need
to run queries in VBA.
The code to use depends on whether it is a simple query (eg select) or an
action query (eg an append).
'Declare the objects/variables you will need
'I am using DAO, you will need to ensure that this reference is enabled. In
VBA go Tools-->References and check on the MS DAO reference.
Dim myQuery As DAO.QueryDef
Dim db As DAO.Database
'**To open a select query
DoCmd.OpenQuery ("my_select_query")
'**To run an "action" query
Set db = CurrentDb
'Make myQuery point to the action query you want to execute
Set myQuery = db.QueryDefs("my_action_query")
'Run it
myQuery.Execute
Most of what you are wanting to do is something that I would personally do
with a macro, I would make all the queries and then use a set of macros to
execute the queries in 1 step (actually probably 2, firstly find the
duplicates and put into a seperate table so I can deal with them, then
another macro to do all the other stuff). Have you considered that option?
To answer some of your questions/asumptions:
Yes, Testname is an abbreviation (fixed output of the instrument) of
the fullname. I had to add this abbreviation to my tbLabtest (ltName)
to be able to make a connection.
Right on that one. tbLabtest has over 100 test's.
Yes, piID is an autonumber...all pk's are autonumbers in my database.
Is there a way to check on duplicate first and last names in
tbPatientInformation before adding them to this table, and then
putting these duplicates in a temp table?
Detecting duplicates is fairly easy, I will show the code below, but I am
not quite sure I understand your comments. You can look for duplicates in
tbPatientInformation, but what do you mean by "before adding them to this
table"? What table are you adding first and last names from
tbPatientInformation into?
Do you perhaps mean look for duplicates in tbResultsTemp? Or are there
other processes you want to do that you have not yet discussed?
To detect duplicates (I deal with appending later) you simply "group" all
the records into first and last name, and then use the "Count" function to
tell you how many entries there are for each group. If there are duplicates
then the count will be >1, so to only show duplicates you add a condition
(where clause) to only display records that have a count > 1. I have
written the code below (again make sure you double check it as I typed it
straight in) for finding duplicates in the tbPatientInformation table. It
would be straightforward to adjust to make it work for a different table.
(1) - just show count for the grouped names
SELECT tbPatientInformation.piLastName, tbPatientInformation.piFirstName,
Count(tbPatientInformation.piFirstName) AS CountOfpiFirstName
FROM tbPatientInformation
GROUP BY tbPatientInformation.piLastName, tbPatientInformation.piFirstName;
(2) - restrict to only show count for duplicate grouped names
SELECT tbPatientInformation.piLastName, tbPatientInformation.piFirstName,
Count(tbPatientInformation.piFirstName) AS CountOfpiFirstName
FROM tbPatientInformation
GROUP BY tbPatientInformation.piLastName, tbPatientInformation.piFirstName
HAVING (((Count(tbPatientInformation.piFirstName))>1));
Finally you would append these results into your new table. I have written
the full SQL below, it runs the SQL to detect shown above and APPENDS into a
new table called tbTempDuplicateNames. To append into this table it assumes
that
1. this table exists
2. this table has at minimum the two fields firstName and lastName.
INSERT INTO tbTempDuplicateNames ( lastName, firstName )
SELECT tbPatientInformation.piLastName, tbPatientInformation.piFirstName
FROM tbPatientInformation
GROUP BY tbPatientInformation.piLastName, tbPatientInformation.piFirstName
HAVING (((Count(tbPatientInformation.piFirstName))>1));
The APPEND will however continue to add the same records every time the
query is run if the duplicates remain, and keep a record of duplicates that
no longer exist. This is because the APPEND just keeps on adding data onto
the bottom of the table, it does not do anything with the existing records
in the table. To only show current duplicates you will need to either clear
out this table right before you run the query (use a DELETE query and clear
out everything) or you could run the query as a MAKE TABLE query rather than
an APPEND query, as this deletes the existing table first.
Delete query SQL:
DELETE tbTempDuplicateNames.*
FROM tbTempDuplicateNames;
Make table query SQL:
SELECT tbPatientInformation.piLastName, tbPatientInformation.piFirstName
INTO tbTempDuplicateNames
FROM tbPatientInformation
GROUP BY tbPatientInformation.piLastName, tbPatientInformation.piFirstName
HAVING (((Count(tbPatientInformation.piFirstName))>1));
"A C" <
[email protected]> wrote in message
Hi
I am not 100% convinced I understand the problem, but let me lay out my
assumptions and then detail the general sql. If my assumptions are wrong
then it may need to be reworked. Furthermore I am not sure if you are
wanting help with the VBA for doing this or just the general procedures and
sql (which is all I have supplied), post back if you want more info.
Assumptions
1a. You import Testname as part of your data, and a record existins in
tbLabtest with a matching ltName.
1b. Furthermore this implies that this application is not dealing with
creating new entries in the tbLabtest table.
2. When you make a new patient record it automatically creates a new piID
somehow (you dont provide details on this - I will assume auto-number).
What you need to do requires 2 steps. Step 1 is creating the patient
record, step 2 is creating the tbTestResults record. They are seperate
steps, you dont do them at the same time, and you need to do step1 before 2.
Step 1 - new patient record.
----------------------------
Firstly search for an existing record for the patients. The following sql
will return the testName (you could use or add different fields if you want)
of all records in tbResultsTemp which do not have existing records in the
tbPatientInformation table.
SELECT [tbPatientInformation].[piID], [tbResultsTemp].[Testname]
FROM tbPatientInformation RIGHT JOIN tbResultsTemp ON
([tbPatientInformation].[piLastName]=[tbResultsTemp].[LastName]) AND
([tbPatientInformation].[piFirstName]=[tbResultsTemp].[FirstName])
WHERE ((([tbPatientInformation].[piID]) Is Null));
If it returns no entries then you can move onto step 2. Each patient is
already in the patient table (which means you can get their piIDs).
If however it returns >1 records then tbResultsTemp contains patients not
yet in tbPatientInformation, you will need to use whatever process you
normally use to create a new patient record for these patients. If their
piIDs are just auto numbers then you could just run an append query to add
these new patients straight from tbResultsTemp into tbPatientInformation,
otherwise you will need to use your normal process for adding these new
patients.
New patients need to be added *before* moving to step 2.
Step 2 - creating records in tbTestResults
------------------------------------------
You now have the following information:
-patient records in tbPatientInformation for each patient in tbResultsTemp
-lab test info in tbLabtest for every test in tbResultsTemp (see assump 1a
and 1b)
All you need to do now is match up the tbResultsTemp records to their
corresponding entry in the above 2 tables to enable you to get the the
correct info (IDs) from each of these tables and push the results into
tbTestResults.
The following SQL will grab out the records from tbResultsTemp AND the
matching ids from tbPatientInformation and tbLabtest and append the result
into tbTestResults
INSERT INTO tbTestResults ( ltID, trResult, trTestDate, piID )
SELECT tbLabtest.ltID, tbResultsTemp.TestResult, tbResultsTemp.TestDate,
tbPatientInformation.piID
FROM (tbLabtest INNER JOIN tbResultsTemp ON tbLabtest.ltName =
tbResultsTemp.Testname) INNER JOIN tbPatientInformation ON
(tbPatientInformation.piFirstName = tbResultsTemp.FirstName) AND
(tbResultsTemp.LastName = tbPatientInformation.piLastName);
Hope that is what you were after.
Post if you are after more info, as mentioned previously I have not detailed
how to execute the SQL in VBA.
Regards
A
I have the following problem
I'll give you an outline first. I have data in a delimited-textfile
from a labinstrument which i wanna add on a daily bases to my own
database used for storing labresults . The relevant tables in my
database are:
tbPatientInformation,: piID (pk), piLastName, piFirstName, piDOB etc
etc.
tbLabtest: ltID (pk), ltName, ltFullName, etc etc
tbTestResults: trID(pk), piID, ltID, trResult, trTestDate etc etc
I managed to import and extract the relevant data (using the
transfertext method) from the textfile into a temp table,
tbImportTemp, with the following fields : Patientname, Testname,
TestResult, TestDate. I then have another temp table, tbResultsTemp,
where i split the patientname into lastname and firstname using a
function (ParseWord, Allen Brown) in an APPEND query. So now i have a
table with the following fields: LastName, FirstName, Testname,
TestResult, TestDate. LastName and FirstName should go into
tbPatientInformation, after grouping them (1 patient can have many
tests). Testname, TestResult and TestDate should go into
tbTestResults. Testname from tbResultsTemp has the same values as
ltName from tbLabtest, but i can't insert it right away into
tbTestResults cuz i need the ltID corresponding to it.
My question is: how do i accomplish this-> Group the patients and then
insert LastName and FirstName into tbPatientInformation and at the
same time inserting the results (Testname, TestResult and TestDate)
into the results table with the appropriate piID and ltID.
Checking for duplicates will be done afterwards when the DOB's and the
rest of the patient information are being entered, but i'll get back
to this in another thread.
I would appreciate any help on this.
