i have about a dozen or so tables each of which has the same pair of fields among others. i was thinking there might be an easy way for the data entry person to enter the values for that pair of fields just once in a simple table with two columns and one row and then let ms access turn water into wine so to speak: i.e., use the info from the simple/reference table as the default value for the two fields in every table which has them. effectively, the simpe/reference table with two columns and one row would be being deployed to provide the default values for the two fields of interest.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
populating 'the same' fields that occur on > 1 tables
- Thread starter uscitizen
- Start date
- Thread starter
- #3
This would be an example
tblOrders
OrdDate <--- First part of primary key
OrdID <--- Second part of primary key
StaffID <--- Foreign key to tblStaff
TableID
tblStaff
StaffID <--- Primary / Foreign key
FName
LName
SSN
Now if you wanted to get the info from tblStaff you would join it like this... tblOrders.StaffId=tblStaff.StaffId. Look up some info on table relationships and design if this doesn't make sense.
tblOrders
OrdDate <--- First part of primary key
OrdID <--- Second part of primary key
StaffID <--- Foreign key to tblStaff
TableID
tblStaff
StaffID <--- Primary / Foreign key
FName
LName
SSN
Now if you wanted to get the info from tblStaff you would join it like this... tblOrders.StaffId=tblStaff.StaffId. Look up some info on table relationships and design if this doesn't make sense.
- Thread starter
- #5
I tried the following:
I created a table called "Project Info" with two fields:
a) Project ID
b) Project Title
and defined a composite key based upon the fields a and b above in that order.
Then I created another table called "Demographics". It contains the two fields in the "Project Info" table above (as non key fields) and uses as a key field "Participant ID". Of course there are other fields too but I won't list them (age, weight, etc. etc).
Per cainemart's suggestion, I drew a relationship from "Project ID" to "Demographics" using the primary key fields in Project ID. I completed one row's worth of information (all I am talking about ever needing since the value of "Project ID" and "Project Title" will be constant for every record in "Demographics"
. I opened "Demographics" table expecting it to display the values of the "Project ID" and "Project Title" entered into the corresponding fields on the "Project ID" table and voila, it didn't :-(
What went awry?
I created a table called "Project Info" with two fields:
a) Project ID
b) Project Title
and defined a composite key based upon the fields a and b above in that order.
Then I created another table called "Demographics". It contains the two fields in the "Project Info" table above (as non key fields) and uses as a key field "Participant ID". Of course there are other fields too but I won't list them (age, weight, etc. etc).
Per cainemart's suggestion, I drew a relationship from "Project ID" to "Demographics" using the primary key fields in Project ID. I completed one row's worth of information (all I am talking about ever needing since the value of "Project ID" and "Project Title" will be constant for every record in "Demographics"
. I opened "Demographics" table expecting it to display the values of the "Project ID" and "Project Title" entered into the corresponding fields on the "Project ID" table and voila, it didn't :-(What went awry?
It will not automatically fill in the ProjectTitle in the demographics table for you. the idea of a relational db is that you have each piece of information once (Project Title) and just display it where you need it.
cainemart is talking about building the relationship between the two tables (on ProjectID) so that when you create queries or forms or reports, information like the ProjectTitle can come along into your resulting display. do you know about queries and report Record Sources?
the gist of the answer for your original question is that you would build a form where for example you want to enter all the demographic details of people on a Project. The record source of the form would be your table ProjectInfo. within that form, you would have a subform based on your Demographic table, and plunk that subform into your main form. the two forms are then related with Master and Child designated as ProjectID. maybe you have never done this and don't 'get it' right now, but essentially you would be entering demographic info that pertains to a certain 'project'. since the master and child links are set up, whenever a user types in something in the 'demographics' subform, the data is entered into the Demographics table along with the ProjectID of that project you are entering data for. you dont do this just by typing into a table. you do it with forms. well, you could do it by just typing into a table, but you have seen the results.
look up:
queries
forms
subforms
relationships
g
cainemart is talking about building the relationship between the two tables (on ProjectID) so that when you create queries or forms or reports, information like the ProjectTitle can come along into your resulting display. do you know about queries and report Record Sources?
the gist of the answer for your original question is that you would build a form where for example you want to enter all the demographic details of people on a Project. The record source of the form would be your table ProjectInfo. within that form, you would have a subform based on your Demographic table, and plunk that subform into your main form. the two forms are then related with Master and Child designated as ProjectID. maybe you have never done this and don't 'get it' right now, but essentially you would be entering demographic info that pertains to a certain 'project'. since the master and child links are set up, whenever a user types in something in the 'demographics' subform, the data is entered into the Demographics table along with the ProjectID of that project you are entering data for. you dont do this just by typing into a table. you do it with forms. well, you could do it by just typing into a table, but you have seen the results.
look up:
queries
forms
subforms
relationships
g
- Thread starter
- #7
after wading through your generous verbiage, it occurred to this writer it may be more straightforward to (prior to initiating a project which will utilize the data base) to just key in the identifying information in the 'default value' are for the fields 'project id' and 'project title' on the demographics (and any other) table(s) to be used by this project. this would be a one time no brainer and would display the same identifying information for every record. i think control fields can be set to remain unchanged (edit off or something) and the tab order could be disabled also making the integrity of the 'project id' and 'project title' a non-issue as well.
whatcha think about that. not elegant, true, but it'd work.
whatcha think about that. not elegant, true, but it'd work.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question