UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Adding Set Of Records Automatically In The Related Table, Access 2007    
 
   
Sailor
post Dec 5 2017, 03:55 AM
Post#1



Posts: 44
Joined: 14-December 16



I have two tables :

Permin_rec table , vaccin_sched table
vaccin_sched related to Permin_rec with a field (child_number) , (one to many)
each record in Permin_rec table should have a set of vaccines that is found in vaccin_sched table
..
now both of the tables are empty
what I need is when a new record is added to Permin_rec a set of specific records should be added automatically to vaccin_sched table
those are : BCG, Rota , Hexa , Paralysis

but I couldn't figure how
any ideas .
Go to the top of the page
 
ranman256
post Dec 5 2017, 06:54 AM
Post#2



Posts: 785
Joined: 25-April 14



append query.
use the select query to pull the source data, and append to target table.
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2017, 07:34 AM
Post#3


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


Obviously, you need more tables than this, so I'm going to assume they exist.

First, there must be a table of vaccines, which is the list of ALL vaccines you would use. This table would include the four you specify: BCG, Rota , Hexa , Paralysis. By the way, these appear to me to be abbreviations. I would expect that this table might have both an abbreviation field and a full name field in which you write out the full, medical name of each vaccine for documentation purposes. In addition, you'll have a designated primary key for vaccines. I suppose these abbreviations could be used for that purpose, but I generally prefer to use the AutoNumber as a surrogate primary key,

You'll also need a way to designate which of the vaccines listed in this vaccine table are required for each type of vaccination event. I don't really understand what "Permin_rec" represents, but I have to assume it's one type of vaccination event which always includes these four vaccines. And I would imagine there are other such events which would call for other groups of one or more vaccines? Is that the case or not? If so, you would have a junction table for that purpose. It would list each type of event, e.g. "Permin_rec" and the vaccine(s) which are required for that event. It contains the Foreign Key from the Vaccine table and the Foreign Key from the vaccination event table.

With that design, you can create an append query that inserts records into the vaccine_sched table from this junction table of vaccination events and associated vaccines.

That's the overview of how this should work. Specifics depend on a bit more detail being available. Am I right in thinking how this works? If so, let's get more details about the tables involved so we can propose an append query to accomplish your goal.

--------------------
Go to the top of the page
 
Sailor
post Dec 5 2017, 09:28 AM
Post#4



Posts: 44
Joined: 14-December 16



I would like to thank every body who suggests here
..
GroverParkGeorge , sure you are much closer from the purpose of my project
please let me put more clarifications
like it's appear from the project , it's a vaccination schedule
the vaccines are not only the four that I have described above , those are only an example
..
Permin_rec is the permanent record for the children's vaccination's situation
it contains :- name , unique number (child_number) , age , gender ,address of child , phone number , ...
Permin_rec have a related table that contains the all possible vaccines
then I would use a specific formula to show only the vaccines should be given , on a daily work form
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2017, 10:15 AM
Post#5


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


You need to have FIVE tables to do this.

First is the Permanent Record table, "permin_rec". This pertains ONLY to the patient. No vaccination information is stored in this table.

Second is the Vaccinations table. This is a list of all possible vaccines.

Third is the Vaccination Events table. This is a list of events which call for vaccinations. For example, you started by listing four vaccines and saying that these four vaccines should be appended to the patient's vaccination schedule as a group. You define that by specifying events on which vaccinations are to be administered, such as the event that calls for this group of four, whatever that is. Let's say that all of your patients get these four vaccines on their second birthday, for example, whatever the rule actually is, of course.

Fourth is the Required Vaccinations table. This table contains the vaccines required for each vaccination event. It contains two fields (and may also have its own Primary Key, depending on your preference). These two fields are the Foreign Keys from one or more Vaccines in the vaccine table and the Foreign Key from the vaccination event to which they are assigned. This type of table is referred to as a Junction table. I assume that each vaccine can be administered in one or more vaccination events. That's what this table does.

Finally, you have the Vaccination Schedule table. This is one you have, but it will be modified. It is also a Junction table. It has two Foreign Key fields as well. One is the Primary Key from the Permanent Record table. This Foreign Key identifies the patient who received the vacation. The other Foreign Key is from the Vaccination Events table. It identifies the event for which vaccinations are administered. Because the Vaccination Events table, in turn, identifies which four vaccines are included, you don't need to record each individual vaccine as well.

So, on the data entry form for patients, you have a subform for vaccination scheduling and within that subform, a combo box listing Vaccination Events. Selecting one then records that event on that patient's schedule.

There is also another way to do this, if you prefer to store each individual vaccine in addition to, or instead of the vaccination event. If that's the case, we can dig into that option in more detail.



--------------------
Go to the top of the page
 
Sailor
post Dec 5 2017, 12:16 PM
Post#6



Posts: 44
Joined: 14-December 16



I just need some fixed and specific records to be added to the related table about 10 records
and that should be happened when a new record is just added to permin_rec
may I ask for some simplicity ?
This post has been edited by Sailor: Dec 5 2017, 12:17 PM
Go to the top of the page
 
projecttoday
post Dec 5 2017, 12:42 PM
Post#7


UtterAccess VIP
Posts: 8,668
Joined: 10-February 04
From: South Charleston, WV


You could use a code-based approach. So you would have something like

Currentdb.Execute "INSERT ... SELECT ", dbFailonError

Search on "Currentdb.Execute" and you will find lots of examples.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2017, 01:07 PM
Post#8


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


Sorry, I was thinking it would useful to make sure the tables supporting this task are set up appropriately first.

So, yes, I would agree that you should do this in VBA, probably in the Click Event of a command button on a form.

Select the patient to whom the vaccinations will be administered and click the button to run a SQL statement like Robert's, with the needed fields included, of course.

--------------------
Go to the top of the page
 
Sailor
post Dec 6 2017, 02:09 AM
Post#9



Posts: 44
Joined: 14-December 16



OK

I have the following query :

strq = "insert into vaccin_sched (vaccin_name,child_number) select vaccin_name, '" & Me.numeric_Val & "' from vaccinTbl;"

I need a Numeric variable criteria ( Me.numeric_Val )
how to do ?
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2017, 06:40 AM
Post#10


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


It depends on the table structure...

--------------------
Go to the top of the page
 
Sailor
post Dec 6 2017, 07:25 AM
Post#11



Posts: 44
Joined: 14-December 16



Solved big_grin.gif
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2017, 07:51 AM
Post#12


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


Congratulations on solving your problem.

Continued success with your project.

For the benefit of anyone reading this discussion later, would you mind showing us your solution?

--------------------
Go to the top of the page
 
Sailor
post Dec 6 2017, 01:55 PM
Post#13



Posts: 44
Joined: 14-December 16



Sure I wouldn't

for the query , instead of

strq = "insert into vaccin_sched (vaccin_name,child_number) select vaccin_name, '" & Me.numeric_Val & "' from vaccinTbl;"

it should be

strq = "insert into vaccin_sched (vaccin_name,child_number) select vaccin_name, " & Me.numeric_Val & " from vaccinTbl;"

for the entire problem I did the following :

I made a table contains all of the vaccine's names
for any new record in permin_rec , a set of all vaccines will be automatically (by the above query) copied to vaccin_sched, that is related to permin_rec
thus any new child's record , will contains a set of all possible vaccine's .
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 06:08 AM