Hi I am building my first database and have come up against a problem. I am running a query across two tables and the criteria is that a field in the second table is null. What I've realised is that this doesn't seem to make sense to ACCESS if their isn't a record for that person in Table 2. The tables are linked in a one-to-one relationship so what I need to happen is that the foreign key in Table 2 be populated from the Primary key in Table 1 so that a record exists. This sounded like a simple thing to want to happen but I can't work out how to do it. Can anyone help. There is already quite a bit of data in Table 1 but more will be added so I need to populate the field for everyone already in there and then again as IDs are added. I hope that makes some sense and someone can help me.
Populating a field in one table from another
- Thread starter Pippa1
- Start date
Sorry. I'll try again.
Table 1 has a field called "Study ID" which is a Primary Key. Table 2 has a field called "Study ID 2" which is the foreign key in Table 2 and these are linked in a one-to-one relationship. So one person has information in both tables but only one row of information. Table 1 currently has lots of data in it. Table 2 has none. I would like each person in Table 1 to have an entry in Table 2 with "Study ID 2" completed.
So if in Table 1 there were five people with STUDY ID's of 1,2,3,4,5 I'd like that to be translated over to Table 2 so there are 5 records where "Study ID 2" is 1,2,3,4,and 5. Because there is quite a bit of data and the IDs are not sequential I don't want to have to enter them into Table 2 by hand.
Does that make more sense?
Table 1 has a field called "Study ID" which is a Primary Key. Table 2 has a field called "Study ID 2" which is the foreign key in Table 2 and these are linked in a one-to-one relationship. So one person has information in both tables but only one row of information. Table 1 currently has lots of data in it. Table 2 has none. I would like each person in Table 1 to have an entry in Table 2 with "Study ID 2" completed.
So if in Table 1 there were five people with STUDY ID's of 1,2,3,4,5 I'd like that to be translated over to Table 2 so there are 5 records where "Study ID 2" is 1,2,3,4,and 5. Because there is quite a bit of data and the IDs are not sequential I don't want to have to enter them into Table 2 by hand.
Does that make more sense?
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 07:08
- Joined
- May 7, 2009
- Messages
- 20,317
initially, you need to create an
Insert query to populate table2:
INSERT INTO table2 ([Study ID2]) SELECT [Study ID] FROM table1;
next you need to modify your Relation.
on the Join Type.. button use, tick:
"Include ALL records from 'Students' and only those records from 'Students2' where the joined fields are equal."
this will come handy when you are adding/updating records
in Table1 through a Form.
Insert query to populate table2:
INSERT INTO table2 ([Study ID2]) SELECT [Study ID] FROM table1;
next you need to modify your Relation.
on the Join Type.. button use, tick:
"Include ALL records from 'Students' and only those records from 'Students2' where the joined fields are equal."
this will come handy when you are adding/updating records
in Table1 through a Form.
Thanks for the replies. I'm trying to work through the insert query.
I know you wouldn't normally have a 1-1. The database is for a research study that has already been running for some time using excel. Table 1 is populated by uploading an excel spreadsheet, as one stage of the study is completed ( a 6 month assessment). Table 2 is where new information will be stored as the study progresses to the next stage ( a 2 year assessment).
The two study IDs are the same number, it's a six digit ID to refer to each person in the study (e.g. 100645) but I was told that the variable should have a different name in the two tables.
I know you wouldn't normally have a 1-1. The database is for a research study that has already been running for some time using excel. Table 1 is populated by uploading an excel spreadsheet, as one stage of the study is completed ( a 6 month assessment). Table 2 is where new information will be stored as the study progresses to the next stage ( a 2 year assessment).
The two study IDs are the same number, it's a six digit ID to refer to each person in the study (e.g. 100645) but I was told that the variable should have a different name in the two tables.
Thanks. I'm afraid I'm too far down the road doing it this way now. I've written all the queries etc. Thank you for trying to improve it but I think it will at least work this way if I can just populate this field.
I'm so sorry but I'm driving myself mad with this.It just says "Syntax Error in INSERT INTO statement". I can't copy and past because my database is in a separate secure area but the below is exactly what I've written:
INSERT INTO T-2 year data ([Study Number 2])
SELECT [Study number]
FROM T_Participant Details
I tried using the ribbon commands to do an append query but all i managed to make this do was generate a load of autonumbers in the T-2 year data table but did not insert the study numbers.
Any ideas anyone?
I've just worked out (OK I know this sounds idiotic) that I can just copy and paste over all the IDs that are already in the database from Table 1 to Table 2 but this does not solve my problem entirely because periodically I will upload another, say 20, people into Table 1 and need this to create the same number of rows in Table 2 with these same study numbers.
Last edited:
Minty
AWF VIP
- Local time
- Today, 00:08
- Joined
- Jul 26, 2013
- Messages
- 10,656
That's not how I read your post. So you have table called (Awful name get rid of the spaces and hyphens - adds some much scope for error and typo's)
Correct?
Code:
[B][T-2 year data] [/B]
[I]Fields[/I]
T2ID - Pk- Autonumber
Study Number 2 - NumberCorrect?
Sorry about the table name but if I change it now I'll have to go and change all the queries etc won't I?
Basically yes the first field is ID which is an autonumber and the second is Study Number 2 which is a 6 digit number that I want to be the same as Study number (the Primary Key in the T_Participant Details table).
Basically yes the first field is ID which is an autonumber and the second is Study Number 2 which is a 6 digit number that I want to be the same as Study number (the Primary Key in the T_Participant Details table).
At what pain point does it become feasible to properly structure your database?
Search this forum for "...just this one issue and I should be fine". Then click on the user's name and follow their post history. You'll find that after that one issue there is many more.
You are hacking around a symptom and not addressing the main issue. I fear this is your "...just this one issue and I should be fine" thread.
- Local time
- Yesterday, 18:08
- Joined
- Feb 28, 2001
- Messages
- 30,310
You are asking for (and probably have already found) trouble by making a 1-to-1 field, particularly if there is a chance that ALL of the data except your proposed PK in the second table can sometimes be null.
Among other things, you should consider that if there is chance for a completely null secondary record, you really DON'T have a 1/1 relationship. You have a 1/many relationship (where in this case, "many" includes "none"). The truth is that the names are terribly misleading, but English common usage governed the name; also it is easier to say "one to many" than "one to any non-negative integer", which is what the relationship REALLY means.
If you have queries involving INNER JOINS and you have a case where you need to add a record to table 2, you can't use the query because you'll get a "no matching record" type of event. The trick with 1/1 tables is that there are times when you CANNOT add that second record because you have already violated relational integrity with respect to any INNER JOIN class of query operation. Even though you never ever will have two records in table 2 for the same PK, if you might have NO records (initially) then you want this to be 1/many.
You talk about "too far down the road to change things" but I fear that the more time you invest in an incorrect structure, the farther you will go down a garden path. Or a desert road.
There is also the old adage about "if you haven't got time to do it right the first time, how will you EVER find time to fix it when it breaks?" And the corollary is "It WILL break if you have a weak area in your design because a chain is only as strong as its weakest link."
Sometimes we give techie advice here. But if you read other posts enough, you will see that we also give procedural or philosophical advice on the design, building, and maintenance of databases based on our own past foibles. Sort of like "If you don't pay attention to history, you are doomed to repeat it" type of thing.
Among other things, you should consider that if there is chance for a completely null secondary record, you really DON'T have a 1/1 relationship. You have a 1/many relationship (where in this case, "many" includes "none"). The truth is that the names are terribly misleading, but English common usage governed the name; also it is easier to say "one to many" than "one to any non-negative integer", which is what the relationship REALLY means.
If you have queries involving INNER JOINS and you have a case where you need to add a record to table 2, you can't use the query because you'll get a "no matching record" type of event. The trick with 1/1 tables is that there are times when you CANNOT add that second record because you have already violated relational integrity with respect to any INNER JOIN class of query operation. Even though you never ever will have two records in table 2 for the same PK, if you might have NO records (initially) then you want this to be 1/many.
You talk about "too far down the road to change things" but I fear that the more time you invest in an incorrect structure, the farther you will go down a garden path. Or a desert road.
There is also the old adage about "if you haven't got time to do it right the first time, how will you EVER find time to fix it when it breaks?" And the corollary is "It WILL break if you have a weak area in your design because a chain is only as strong as its weakest link."
Sometimes we give techie advice here. But if you read other posts enough, you will see that we also give procedural or philosophical advice on the design, building, and maintenance of databases based on our own past foibles. Sort of like "If you don't pay attention to history, you are doomed to repeat it" type of thing.
Thank you for trying to guide me.
There will never be a situation where there isn't data in the second table. Every single person in the first table needs to have data in the second table as well.
I made it like this because of trying to keep it simple when uploading the data that comes from an excel sheet into the first table, because this seems very prone to failing and so I thought if I had exactly the same fields in the access table as in the excel sheet it would have the best chance. Is that just a rookie error?
There will never be a situation where there isn't data in the second table. Every single person in the first table needs to have data in the second table as well.
I made it like this because of trying to keep it simple when uploading the data that comes from an excel sheet into the first table, because this seems very prone to failing and so I thought if I had exactly the same fields in the access table as in the excel sheet it would have the best chance. Is that just a rookie error?
Yes. Data dictates table structure, that's it. Not how you want forms set up, not how you receive the data. The data itself dictates the table structure.
Perhaps you can set up the Relationship tool in Access then post a screenshot of it so we can see the tables/fields you have.
Perhaps you can set up the Relationship tool in Access then post a screenshot of it so we can see the tables/fields you have.
That works. Big to small, here's the big issues I see:
1. Numerated field names. When you start adding numbers to field names to differentiate them, it's time for a new table. All that consent call data needs to go into a new table with this structure:
ConsentCalls
cc_ID, autonumber, primary key
ID_t2, number, foreign key to [T-2 year data].ID field
Call, same field type as [Reminder consent call 1] field, will hold its data
Notes, same field type as [Reminder consent call 1 notes], will hold its data
That's it, just 4 fields. That means when you have 8 consent calls you add 8 records in this new table.
2. Seperate date and time fields. A date/time field can hold both of those pieces of data, you shouldn't store them seperatly. That means [Recall 1 expected date] and [Recall 1 expected time] are not both necessary, just one of them which will hold both pieces of data. Doing this will allow you to easily use the Date/Time functions access has.
3. Lots of events in one table. T-2 year data looks like its essentially a project management table--lots of dates. Instead of a new field for each event, I think you should use a seperate table where you store the event not in a field name but in the field value. For example:
Events
event_ID, autonumber, primary key of table
ID_t2, number, foreign key to [T-2 year data].ID field
event_Type, text, will hold value currently in field names (e.g. Consent form sent, Consent form received, Recall 1 expected, etc.)
event_Date, Date/Time, date/time the event occured
Again, 4 fields and that's it. Instead of 18 fields for events in [T-2 year data] you would have 18 records in Events to accomodate that data.
I suggest searching this forum for an 'Events Database' or 'Project Management'. I'm sure someone has an example that you can steal for your own purpose.
4. Only use alpha-numeric characters in names. Coding and querying will be easier if you follow this rule. That means, remove all spaces and dashes from table/field names.
1. Numerated field names. When you start adding numbers to field names to differentiate them, it's time for a new table. All that consent call data needs to go into a new table with this structure:
ConsentCalls
cc_ID, autonumber, primary key
ID_t2, number, foreign key to [T-2 year data].ID field
Call, same field type as [Reminder consent call 1] field, will hold its data
Notes, same field type as [Reminder consent call 1 notes], will hold its data
That's it, just 4 fields. That means when you have 8 consent calls you add 8 records in this new table.
2. Seperate date and time fields. A date/time field can hold both of those pieces of data, you shouldn't store them seperatly. That means [Recall 1 expected date] and [Recall 1 expected time] are not both necessary, just one of them which will hold both pieces of data. Doing this will allow you to easily use the Date/Time functions access has.
3. Lots of events in one table. T-2 year data looks like its essentially a project management table--lots of dates. Instead of a new field for each event, I think you should use a seperate table where you store the event not in a field name but in the field value. For example:
Events
event_ID, autonumber, primary key of table
ID_t2, number, foreign key to [T-2 year data].ID field
event_Type, text, will hold value currently in field names (e.g. Consent form sent, Consent form received, Recall 1 expected, etc.)
event_Date, Date/Time, date/time the event occured
Again, 4 fields and that's it. Instead of 18 fields for events in [T-2 year data] you would have 18 records in Events to accomodate that data.
I suggest searching this forum for an 'Events Database' or 'Project Management'. I'm sure someone has an example that you can steal for your own purpose.
4. Only use alpha-numeric characters in names. Coding and querying will be easier if you follow this rule. That means, remove all spaces and dashes from table/field names.
OK Wow, thank you.
I need to take a minute (or a day) to process all of that but my initial question is about the consent table.
That makes sense to me but I need a form that shows all the consent calls that have been made for one person, and I know you said that's not how I should plan the tables but... how do I create the query that the form is based on to do that? It would need to pull data from multiple rows of one table into the same form. I don't doubt that is possible but I don't think I know how to do it.
I need to take a minute (or a day) to process all of that but my initial question is about the consent table.
That makes sense to me but I need a form that shows all the consent calls that have been made for one person, and I know you said that's not how I should plan the tables but... how do I create the query that the form is based on to do that? It would need to pull data from multiple rows of one table into the same form. I don't doubt that is possible but I don't think I know how to do it.
