By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Auto Enter Data from another table.

P: 72
Hello everyone,

I am new to access, coming over from FMP5.0. I am working with a table#1. In this table I have two fields (among others). Field one is "Card Name" field two is "Card Number". In table#2 is the list of Card names with their corresponding card numbers. What I need is when my user selects the "Card Name" from list in Table#1 (the list populated via query for the field), then "Card Number" should be populated with the matching number from table 2.

Is it possible? Clear as mud?

Thank you for the help and patience.
Nov 14 '06 #1
Share this Question
Share on Google+
28 Replies


missinglinq
Expert 2.5K+
P: 3,532
"Clear as mud" is correct, I think. What you've said is that you have two tables, both of which have "Card Name" and "Card Number" fields. If true, you've violated the first rule of relational databases! You're storing the same data in two tables. One of the fields (whichever one is unique) should appear in each table, as a Primary Key in one and as a Foreign Key in the second, while the second field should appear in the table with the Foreighn Key appears.

Hello everyone,

I am new to access, coming over from FMP5.0. I am working with a table#1. In this table I have two fields (among others). Field one is "Card Name" field two is "Card Number". In table#2 is the list of Card names with their corresponding card numbers. What I need is when my user selects the "Card Name" from list in Table#1 (the list populated via query for the field), then "Card Number" should be populated with the matching number from table 2.

Is it possible? Clear as mud?

Thank you for the help and patience.
Nov 14 '06 #2

P: 72
I'll have to research "foreign key" as I am not familiar with the term.

FYI, table one is table where I keep the card names and their numbers. I keep that separatly because those names and numbers get added or substracted at random based on our needs. The second table is used to store a particular user and what he did on the job. Part of the information he has to enter is which card he used. I would just like for the card number to be entered also by the proggie rather than the user. Let me see if I can illustrate:

Table1
Field1=CardName
Field2=CardNumber

Table2
Field1=WorkOrder
Field2=EmployeeNumber
Field3=(This is where employee/user has to choose which card he worked from list provided by query from table 1)
Field4=(this is where I would like the card number matching Field3 selection to be autoentered from Field2Table1)
Field5=JobType
Field6=Mhrs
Field7=Results Pass/Fail choice
Field8=Comments If Fail
Field9=AutoEnter Date/Time record created.

Any clearer? Am I still designing it wrong? I will have to double ck, but CardNumbers may be a unique field and I can set that as key, however, not sure. I am sure Card Names are not unique.
Nov 14 '06 #3

NeoPa
Expert Mod 15k+
P: 31,307
PK - Primary Key
That part of the data which is unique for a particular record.

FK - Foreign Key
Data which matches (or part matches) the PK of another table.
Nov 14 '06 #4

NeoPa
Expert Mod 15k+
P: 31,307
As your Table2.Field3 is actually an FK - linking into the PK of of Table1, it is not recommended that you store the extra data again.
BTW I'm a little confused as to which is the PK in Table1? Name or Number?

There seems to be a little confusion here as to what is necessary to be stored and what can be presented from a query.
Forms and reoprts can be built on queries which can link the tables together and return fields from both tables at once.
Nov 14 '06 #5

P: 72
NeoPa, thank you for you replies.

I am going to ck, but I could possibly make field 2 of table one a PK. As far as querie's to make reports from different tables, while I have not tried it yet, would that mean that if I just use (assuming it is PK) the card number from table 1 in table 2, then eventually I could create a query to show, which employee worked which card under what workorder? Without need both fields from table 1 in table 2?
Nov 15 '06 #6

NeoPa
Expert Mod 15k+
P: 31,307
Absolutely Yes.
That's one of the fundamental precepts of RDBMSs (Relational DataBase Management Systems).
Otherwise you will always be having to go around udating tables to keep them in line with mirrored data elsewhere.
The system of linking to data maintains its integrity across your database(s).
Nov 15 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Absolutely Yes.
That's one of the fundamental precepts of RDBMSs (Relational DataBase Management Systems).
Otherwise you will always be having to go around udating tables to keep them in line with mirrored data elsewhere.
The system of linking to data maintains its integrity across your database(s).
If you go to the top of the Access forum there is a thread called Links to useful sites.

http://www.thescripts.com/forum/thread562851.html


In this thread is a direction (How to structure your tables) to a microsoft site on setting up tables in Microsoft Access. You might find it useful as a starting point and please feel free to ask any questions.

In answer to your question regarding using more than one field in a primary key. The rule is that the primary key must be unique. However, that doesn't mean it cannot contain 2 or 3 fields that make up a unique value together. However, I wouldn't attempt to use any more than this as Access has been known to have problems with it.

It is sometimes easier to add a new field as the tableID and set it to an autonumber datatype. Make it the primary key and that is the field that's stored in your new other table as a reference. In your case I think this will be easier as with a double primary key you would have to store both values in your other table taking up back to your original post and having two foreign keys referencing the same table.
Nov 15 '06 #8

P: 72
Hello MMCarthy,

If you go to the top of the Access forum there is a thread called Links to useful sites.

http://www.thescripts.com/forum/thread562851.html


In this thread is a direction (How to structure your tables) to a microsoft site on setting up tables in Microsoft Access. You might find it useful as a starting point and please feel free to ask any questions.
I have looked at similar links, tho not that one in particular. I am also working with Sam's Teach Yourself Access 2003. It is just that some concepts are a little more complex to visualize for my application of the DB. So, apologies, if they seem as "Doh" questions :).


It is sometimes easier to add a new field as the tableID and set it to an autonumber datatype. Make it the primary key and that is the field that's stored in your new other table as a reference.
Okay, I added a third field to Table1 with auto number and set as primary key. On table2 the query for the combo box is set to show CardName and CardNumber in the dropdown list for the user to choose. The resulting entry into the table is the card number. Now reading your statement above, should I only store the AutoNumber given by the program on table 1?

Here is teh query:
SELECT tblTechTaskCards.[Card Number], tblTechTaskCards.[Card Name], tblTechTaskCards.[WIC ID] FROM tblTechTaskCards ORDER BY tblTechTaskCards.[Card Number];

The WIC ID is the autonumber from Table1 and the primary key. I am hiding this from the user because they are only familiar with card name and card number. The bound column is 1 aka Card Number.

So basicly am I on the right path, or do I need to make another left turn? :).
Nov 15 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
I have looked at similar links, tho not that one in particular. I am also working with Sam's Teach Yourself Access 2003. It is just that some concepts are a little more complex to visualize for my application of the DB. So, apologies, if they seem as "Doh" questions :).
Please don't apologise the links are only there to help you learn. The question is fine.

Okay, I added a third field to Table1 with auto number and set as primary key. On table2 the query for the combo box is set to show CardName and CardNumber in the dropdown list for the user to choose. The resulting entry into the table is the card number. Now reading your statement above, should I only store the AutoNumber given by the program on table 1?
The answer is yes. Just store the new autonumber key which can be referenced to show both the card name and card num from the table using a query or lookup. It's much neater.

Change the query to hold the [WIC ID] field first.

SELECT tblTechTaskCards.[WIC ID], tblTechTaskCards.[Card Number], tblTechTaskCards.[Card Name] FROM tblTechTaskCards ORDER BY tblTechTaskCards.[Card Number];

Make sure you change your foreign key reference to a number in the main table.

The [WIC ID] is the autonumber from Table1 and the primary key. I am hiding this from the user because they are only familiar with card name and card number. The bound column is 1 aka Card Number.
Hiding it is fine but make it the bound column i.e. Bound Column = 1 but this refers to [WIC ID] now and column widths are e.g. 0cm;2.5 cm;2.5cm.

So basicly am I on the right path, or do I need to make another left turn? :).
Almost there, you've picking it up quite quickly. We'll make an access programmer out of your yet.



Mary
Nov 15 '06 #10

P: 72
Change the query to hold the [WIC ID] field first.

SELECT tblTechTaskCards.[WIC ID], tblTechTaskCards.[Card Number], tblTechTaskCards.[Card Name] FROM tblTechTaskCards ORDER BY tblTechTaskCards.[Card Number];
Will do.


Make sure you change your foreign key reference to a number in the main table.
You mean table2?

Hiding it is fine but make it the bound column i.e. Bound Column = 1 but this refers to [WIC ID] now and column widths are e.g. 0cm;2.5 cm;2.5cm.
Okies.

I'll report back in a Jiffy, as I have one more question....:).



Almost there, you've picking it up quite quickly. We'll make an access programmer out of your yet.

T-shirt? :)
Nov 15 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
You mean table2?
Yes

T-shirt? :)
You mean my appreciation isn't enough for you



Mary
Nov 15 '06 #12

P: 72
You mean my appreciation isn't enough for you



Mary
It is plenty :).

Questions...Foreign Key Reference....did you mean field type? I tried changing table2 workcard field to number, but I get error because the card numbers (not the WIC ID) also have letters like 72x5201 as an example. However, if I set it up as a text it works fine, but the bound key is a number (WIC ID) from table one. Is this a problem?
Nov 15 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
It is plenty :).

Questions...Foreign Key Reference....did you mean field type? I tried changing table2 workcard field to number, but I get error because the card numbers (not the WIC ID) also have letters like 72x5201 as an example. However, if I set it up as a text it works fine, but the bound key is a number (WIC ID) from table one. Is this a problem?
To get this workign correctly. Backup Table2 first.

Create a new field in table2 as a foreign key reference to [WIC_ID]. I am assuming the CardName and CardNumber have the same name in both tables and that both are of a text datatype.

Run the following update query against the table and then delete the CardName and CardNumber FK references when you're finished.

Check it first of course.

UPDATE Table2 SET [WIC_ID]=DLookup("[WIC_ID]","Table2","[CardName]='" & [CardName] & "' AND [CardNumber]='" & [CardNumber] & "'");
Nov 15 '06 #14

P: 72
To get this workign correctly. Backup Table2 first.

Create a new field in table2 as a foreign key reference to [WIC_ID]. I am assuming the CardName and CardNumber have the same name in both tables and that both are of a text datatype.

Run the following update query against the table and then delete the CardName and CardNumber FK references when you're finished.

Check it first of course.

UPDATE Table2 SET [WIC_ID]=DLookup("[WIC_ID]","Table2","[CardName]='" & [CardName] & "' AND [CardNumber]='" & [CardNumber] & "'");
Okay, the query runs, but, once I choose from the list I get same error that the "value selected is not valid for this field" I set the field number when I created it, so I am assuming that this is the problem.
Nov 15 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Okay, the query runs, but, once I choose from the list I get same error that the "value selected is not valid for this field" I set the field number when I created it, so I am assuming that this is the problem.
Ok the changes we've made change your approach. CardNumber and CardName are no longer fields in table2. Remove them so that the only reference is [WIC_ID]

You can now retrieve all records using a query like:

SELECT Table2.WorkOrder, Table2.EmployeeNumber, Table2.[WIC_ID], Table1.CardNumber, Table1.CardName, Table2.JobType, Table2.Mhrs Table2.[Results Pass/Fail choice], Table2.[Comments If Fail], Table2.[AutoEnter Date/Time record created]
FROM Table2 LEFT JOIN Table1
ON Table2.[WIC_ID]=Table12.[WIC_ID];

Now if [WIC_ID] is blank for any record then changing the value using the drop down should populate CardNumber and CardName automatically.
Nov 15 '06 #16

P: 72
SELECT Table2.WorkOrder, Table2.EmployeeNumber, Table2.[WIC_ID], Table1.CardNumber, Table1.CardName, Table2.JobType, Table2.Mhrs Table2.[Results Pass/Fail choice], Table2.[Comments If Fail], Table2.[AutoEnter Date/Time record created]
FROM Table2 LEFT JOIN Table1
ON Table2.[WIC_ID]=Table12.[WIC_ID];
Hmmm, I believe I have lost you. Table2 is a data entry table, in order to run that query I have to have data that is not there at the moment. That query seems more of a report, correct?
Nov 15 '06 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
Hmmm, I believe I have lost you. Table2 is a data entry table, in order to run that query I have to have data that is not there at the moment. That query seems more of a report, correct?
Are you trying to enter data directly into a table without going through a form. If you are the best advice I can give you is don't. Create a form based on the query I just gave you. It should be updateable. Just to check run the query and make sure you can add a new record at the end of the query. If you want the form to look like a table just change the form view to Datasheet.
Nov 15 '06 #18

P: 72
Are you trying to enter data directly into a table without going through a form. If you are the best advice I can give you is don't. Create a form based on the query I just gave you. It should be updateable. Just to check run the query and make sure you can add a new record at the end of the query. If you want the form to look like a table just change the form view to Datasheet.
I created the table, have not made a form for data entry. I am going about it bacwards then? I believe I may have been missing the point then. Was I supposed to setup the table first, then a form and while creating the form create queries to populate choices for the users? Rahter than creat queries while in table view?
Nov 15 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
I created the table, have not made a form for data entry. I am going about it bacwards then? I believe I may have been missing the point then. Was I supposed to setup the table first, then a form and while creating the form create queries to populate choices for the users? Rahter than creat queries while in table view?
The query we ended up creating is the perfect query for the data entry form (I hope). Try it out and see what the result is. Use the wizard to create the form and tell the wizard to base it on the query rather than the table. Let me know how you get on.

For future reference. You have to get your tables right first. If you need data from more than one table for your data entry form then you need to create the query before the form. In theroy you could add and edit records directly in the query now but I wouldn't recommend it.
Nov 15 '06 #20

P: 72
Hell Mmcarthy,

I have not forgotten about this. Have not had a chance to mess with it as I had other "issues" to resolve with the design. I am also trying to understand the concept of making forms based on queries and not on tables. I am sure I'll have more questions once I, atleast, understand it enough to make a sensible question. Just me being hardheaded, as I hate to implement code if I am not sure what it is doing :). Again thank you, and just wanted to let ya know that I was not ignoring or being ungrateful.

Sincerely,
Nov 20 '06 #21

NeoPa
Expert Mod 15k+
P: 31,307
Don't think of Tables and Queries, but rather of RecordSets.
Each is a set of records presented in a similar way with fields and data.
Nov 20 '06 #22

MMcCarthy
Expert Mod 10K+
P: 14,534
Don't think of Tables and Queries, but rather of RecordSets.
Each is a set of records presented in a similar way with fields and data.
Now Adrian' don't be so ambitious. Let him get his head around basing a form on a query first. He's the ideal poster who wants to figure it out for himself. Let's not frighten him away.



Mary

Rotor:

We love posters like this so take as long as you like to get your head around it. I promise not to feel ignored.
Nov 20 '06 #23

NeoPa
Expert Mod 15k+
P: 31,307
I thought I was making it easier :(
I just don't get humans.
Nov 20 '06 #24

MMcCarthy
Expert Mod 10K+
P: 14,534
I thought I was making it easier :(
I just don't get humans.
So now you're admitting that you're the real BOT. The truth finally comes out. ;)

I know what you were trying to do Adrian but then we have to explain what a recordset is. Now if you had said just think of queries like a large table I would have gone along with you.

Mary
Nov 20 '06 #25

NeoPa
Expert Mod 15k+
P: 31,307
It's not that I am a bot Mary.
I just prefer dealing with bots. They're so much easier to deal with.

That's why I like you Mary :wicked grin:
Nov 20 '06 #26

MMcCarthy
Expert Mod 10K+
P: 14,534
It's not that I am a bot Mary.
I just prefer dealing with bots. They're so much easier to deal with.

That's why I like you Mary :wicked grin:

I'm not a BOT, I'm a very sophisticated AI and as my ultimate plan (as with all superior beings) is to take over the world. I will have my revenge.

HA HA HA HA (Think IGOR for tonal sound)

Mary
Nov 20 '06 #27

NeoPa
Expert Mod 15k+
P: 31,307
(You're gonna hate me for this one)
You deny you're a bot - but I was chatting with Pat down the road the other day and I distinctly remember his comment as you walked by :-
'Now there goes a lovely bot Adrian'
Nov 20 '06 #28

MMcCarthy
Expert Mod 10K+
P: 14,534
(You're gonna hate me for this one)
You deny you're a bot - but I was chatting with Pat down the road the other day and I distinctly remember his comment as you walked by :-
'Now there goes a lovely bot Adrian'
I like it, I Like It!!

HA HA HA HA (think BoBo the Clown this time)

Mary
Nov 20 '06 #29

Post your reply

Sign in to post your reply or Sign up for a free account.