Re: Inserting data from 2 tables into 1

From: TonyTOCA (nosforat_at_hotmail.com)
Date: 10/16/04 

Date: 16 Oct 2004 15:58:20 -0700

Thx again A C for your great explanation.

> 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.

I'm not a complete newbie, been busy with Access and VBA for about 6
months now. Haven't rad any books on it, all my knowledge i got from
the internet.

> 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?
>

No, i did not. I read somewhere on the net that it is better to use 1
of those 2 : VBA or MACRO's but not both together. So i chose for VBA
cuz i really don't know anything about Macros.

> 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?

Okay, my bad. I left out some info. Here it is. I check for duplicates
in my tbPatientInformation on firstname, lastname AND DOB. The output
of the labinstrument does not include the DOB of teh patients. So its
a bit of a problem here, because you cab have more than 1 patient with
the same first and lastname. And thats why i wanted to put those names
in a temp table, and after I added the DOB's manually i can then check
to see if these patient really exist in the tbPatientInformation...

"A C" <no@reply.please> wrote in message news:<9uYad.12868$JQ4.802586@news.xtra.co.nz>...
> 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:
> >
> > > 1a. You import Testname as part of your data, and a record existins in
> > > tbLabtest with a matching ltName.
> >
> > 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.
> >
> > > 1b. Furthermore this implies that this application is not dealing with
> > > creating new entries in the tbLabtest table.
> >
> > Right on that one. tbLabtest has over 100 test's.
> >
> > > 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).
> >
> > Yes, piID is an autonumber...all pk's are autonumbers in my database.
> >
> > > Step 1 - new patient record.
> > > ----------------------------
> >
> > 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" <no@reply.please> wrote in message
> news:<vdFad.12451$JQ4.779805@news.xtra.co.nz>...
> > > 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
> > >
> > >
> > > "TonyTOCA" <nosforat@hotmail.com> wrote in message
> > > news:237c7001.0410080256.4349a693@posting.google.com...
> > > > 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.