Add records to a table based upon values from another table using a fo 1

[lastdairyman]

How do I retrieve data from one table to place in another table based upon the an entry I type in my master form?

Here is my example:

I have a table called tblpen#tx# with three fields. ID, PEN# and TX#.

The ID is numbered 1-96, the pen# is numbered 1-96, the tx# is randomly numbered using the values 1-12. For example pen# 1 has tx#7 assigned to it, pen# 2 has tx#10 assigned to it, etc..... until all 96 pen#s have a tx#. In total each tx# is assigned to 16 pens randomly place for a total of 96 pens.

I have a master form that I enter data into such as pen#, date, weight of animal, observation code, ect..... I wish for the form to automatically display the tx# based upon the pen# I enter. I then wish for the all of the data to go into my master database table.

Can this be done with this extra data source? It would save me a tremendous amount of typing. I have over 2,000 entries to type in. This would save me at least that many keystrokes.

 

[RiverGuy]

I'm having a hard time understanding exactly what you're trying to do.

Each pen can have only 12 tx's, and those 12 tx's cannot be in another pen?

Or can duplicates appear?

I have an idea, but need to know more.

 

[lastdairyman]

Sorry for the confusion.

96 pens each has a tx#. That number is either 1,2,3,4,5,6,7,8,9,10,11,or 12.

Below is the table. I want my master form to get the tx# from this table based upon the number I enter in my master form. The master form inturn updates my master table with that value. If will notice pens 2 & 11 have the same treatment (tx#) # assigned to them.

I'm thinking something like a nested formula in Excel tp produce this value.

At the bottom of this reply is part of my master table I wish for Access to place the tx#. I've already entered the tx#s on it.

Thanks for looking at this.

Regards


ID Pen # TX #
1 1 9
2 2 11
3 3 5
4 4 3
5 5 2
6 6 1
7 7 6
8 8 9
9 9 3
10 10 8
11 11 11
12 12 5
13 13 12
14 14 1
15 15 3
16 16 7
17 17 2
18 18 10
19 19 3
20 20 7
21 21 5
22 22 2
23 23 4
24 24 1
25 25 11
26 26 12
27 27 6
28 28 10
29 29 9
30 30 8
31 31 4
32 32 9
33 33 6
34 34 8
35 35 5
36 36 11
37 37 10
38 38 7
39 39 4
40 40 12
41 41 1
42 42 2
43 43 10
44 44 12
45 45 7
46 46 6
47 47 8
48 48 4
49 49 5
50 50 4
51 51 6
52 52 1
53 53 2
54 54 12
55 55 7
56 56 10
57 57 5
58 58 11
59 59 1
60 60 4
61 61 10
62 62 7
63 63 1
64 64 6
65 65 5
66 66 4
67 67 7
68 68 3
69 69 6
70 70 4
71 71 11
72 72 5
73 73 9
74 74 10
75 75 12
76 76 2
77 77 8
78 78 1
79 79 9
80 80 11
81 81 12
82 82 8
83 83 2
84 84 3
85 85 9
86 86 2
87 87 8
88 88 3
89 89 12
90 90 6
91 91 10
92 92 11
93 93 9
94 94 8
95 95 3
96 96 7

Below is my master table that I'm generating.


# Date Pen # Tx # Bird # Bird Wt Location
1 19-Jun-02 47 8 1 .086 SE C
2 19-Jun-02 47 8 2 .086 SE C
3 19-Jun-02 47 8 3 .076 SE C
4 19-Jun-02 47 8 4 .034 SE C
5 19-Jun-02 15 3 1 .064 NE C
6 19-Jun-02 32 9 1 .078 SE C

 

[RiverGuy]

Oh OK, so you're saying that when you change the tx in the first table, you want them changed in the second table too?

If that is the case, I would think it would be easier to delete the tx in the master table, and make a query, that joins on the pen #, and use that query to show your output results.

 

[lastdairyman]

I'm saying when I change the pen# in the master table I wish for access to look up the correct tx# for the pen# I enter and automaticly fill in the data for the tx# data box that would then write it to the master table.

I have about 2,000 birds that have data to be entered. I only have 96 pens that these birds are coming out of. I was thinking in the terms of a "nested" formula like in Excel where it states...... if(a1=pen 34, then 'tx 7').

Hope this is easier to understand.

I'm animal scientist trying to simply my data entry procedures.

Thanks again for your help.

 

[RiverGuy]

I gotcha. So you want to change the pens, and have the tx automattically changed. I still say that you don't need the tx field in your master table.

Because each pen can only have 1 tx, the integrity of the database would be sacrificed if a pen/tx relationship were in more than one place.

Also, the tx would depend on the pen in the master table, and not the primary key, which isn't very normalized.

So this is what I think you should do.....
(And I'll explain this is easy to follow on the screen terms if I can).

1. Delete the tx field from your master table.
2. Open the relationships windows and (making sure that the pen is the key in your pen table), draw a relationship from the pen table to your master table. This should be a one to many relationship.
3. Open a query window and add your master table and the pen table. The fields you will want to choose to display will be everything from your master table and JUST the tx field from the pen table.

You can run this, make reports off this query, export it to excel, do whatever, just like its a table. If you go and edit the master table and change a pen #, the next time you run that query, or a report that is connected to it, it will show the new tx.

Hope that helps. If you need anymore help, you can email me a copy of the db without data.

 

[lastdairyman]

I just knew that was the solution. But I keep getting a "type mismatch expression" error when I try and run the query you instructed me to create.

I deleted the TX# from the master table and made the pen# in the pen/tx table the key.

I created the relationship you describe by dragging the pen# from the pen/tx table to the pen# in the master table.

Set up the query with just the tx# from the pen/tx table and all of the fields from the master table.

error messages quickly ensude

I'm doing my final weigh on the birds this Friday morning and need to get all of this data in so I calculate Feed Conversions.


Any Ideas???

 

[RiverGuy]

The pen # in both tables must be the same data type. See if one is text, and one is number, try making them the same number type.

But theorettically, it shouldn't allow you to even make a relationship if they are a different type, AFAIK.

You can also try dropping the pen # from the query and using the pen # and tx from the pen table, instead of just the tx.

It's hard to tell if there is something out of place, something small or something like that.

One other thing to try would be if you are selecting the * for all columns from the master table, try selecting all the columns individually one at a time for your query. (Still having all the columns in the query, but not picking the * for all columns).

But I made a model of what you are trying to do, and it works out great.

 

[lastdairyman]

RiverGuy,

I don't think I'm getting across what I need to do. What files can I send you?

Can you send me a copy of the model you said is working fine?

The pen/tx table only has 96 values in three different feilds. They are the auto number id, the pen # itself 1-96 ( these do not repeat) simply 1,2,3,4,5,....96. And then a tx number assigned to each pen. I'm using the number range of 1-12. The tx number appears 8 times randomly throughout the 96 pens. Another words pen 1 may be a tx 3 and pen 33 may be a tx 3 ect.....

Then I created a form that I'm entering data on each bird I remove from a said pen. Whenever I type in the pen number on this form I wish for access to locate or lookup the correct tx for that pen and enter it automaticly.

Sorry for being such an newbie.

LastDairyman

 

[RiverGuy]

Before I try and explain further, let me ask you one thing--is the tx for each Pen not static? Meaning that at some times you change which tx is in each Pen?

 

[lastdairyman]

The tx remains the same for each pen throughout the expirement. I never change the tx number once I begin the research.

Below is the information I sent earlier.

ID Pen # TX #
1 1 9
2 2 11
3 3 5
4 4 3
5 5 2
6 6 1
7 7 6
8 8 9
9 9 3
10 10 8
11 11 11
12 12 5
13 13 12
14 14 1
15 15 3
16 16 7
17 17 2
18 18 10
19 19 3
20 20 7
21 21 5
22 22 2
23 23 4
24 24 1
25 25 11
26 26 12
27 27 6
28 28 10
29 29 9
30 30 8
31 31 4
32 32 9
33 33 6
34 34 8
35 35 5
36 36 11
37 37 10
38 38 7
39 39 4
40 40 12
41 41 1
42 42 2
43 43 10
44 44 12
45 45 7
46 46 6
47 47 8
48 48 4
49 49 5
50 50 4
51 51 6
52 52 1
53 53 2
54 54 12
55 55 7
56 56 10
57 57 5
58 58 11
59 59 1
60 60 4
61 61 10
62 62 7
63 63 1
64 64 6
65 65 5
66 66 4
67 67 7
68 68 3
69 69 6
70 70 4
71 71 11
72 72 5
73 73 9
74 74 10
75 75 12
76 76 2
77 77 8
78 78 1
79 79 9
80 80 11
81 81 12
82 82 8
83 83 2
84 84 3
85 85 9
86 86 2
87 87 8
88 88 3
89 89 12
90 90 6
91 91 10
92 92 11
93 93 9
94 94 8
95 95 3
96 96 7


Pens remain constant and tx remains constant. Nothing is static in the entire database. I just wish for access to help when entering my data.

For example a typical data entry would be (the words and numbers in the [] is what I would type or wish for access to auto enter.

Date [19-Jun-02], then Pen # [47] , then Tx # [this is what I want automaticly, in this case 8] , Bird # [1] then Bird Wt [.086] , location [SE]

Regards,

LastDairyman

 

[RiverGuy]

To do this right, you need 3 tables.

A Pen table
A Bird table
A transaction table

Pen Table
__________
Pen # --Primary key
tx#


Bird Table
__________
Bird #--Primary Key Any other Fields that depend on ONLY the bird (no tx, no Pen #)


Bird-Pen Int. Table
__________
Date--All 3 of these are the primary key
Bird #
Pen #

Relationships you need
__________
Pen# to Bird-Pen Int. (1 Pen# to Many Pen#)
Bird# to Bird-Pen Int. (1 Bird# to Many Bird#)

You can then enter the data into the Bird-Pen Int. table. The only data you need to enter is the Date, the Bird # and the Pen #. Access will already know what tx#, what the Bird's name is, or whatever fields you have associated with a bird.

It just won't show it in the table. That's where queries, or "views" come in.

Say you have a record for August 8 in the Int. table with Pen # 20 and Bird # 10.

You want to view that whole record, but also want to see the tx # and Bird's name, or whatever.

You pop open a query window. You add the Pen table, the Bird table, and the Bird-Pen Int. table. You select the Date, Bird#, Pen# from the Int. table. You select the tx#, the Bird name, or whatever from the other tables. You save the view. This view shows everything you need to see. That's it. All you ever need to do is enter in the date, bird# and pen#. Maybe you would have a bird weight field in the Int. table if the weight changes. But if that is the case, its just like a date. Dates can change, and weight can change.

Do you get this concept at all? If not, when do you need this done by, and if not, give me your email address, and I'll email you, I'll send you some pictures of what it looks like.

 

[RiverGuy]

To do this right, you need 3 tables.

A Pen table
A Bird table
A transaction table

Pen Table
__________
Pen # --Primary key
tx#


Bird Table
__________
Bird #--Primary Key Any other Fields that depend on ONLY the bird (no tx, no Pen #)


Bird-Pen Int. Table
__________
Date--All 3 of these are the primary key
Bird #
Pen #

Relationships you need
__________
Pen# to Bird-Pen Int. (1 Pen# to Many Pen#)
Bird# to Bird-Pen Int. (1 Bird# to Many Bird#)

You can then enter the data into the Bird-Pen Int. table. The only data you need to enter is the Date, the Bird # and the Pen #. Access will already know what tx#, what the Bird's name is, or whatever fields you have associated with a bird.

It just won't show it in the table. That's where queries, or "views" come in.

Say you have a record for August 8 in the Int. table with Pen # 20 and Bird # 10.

You want to view that whole record, but also want to see the tx # and Bird's name, or whatever.

You pop open a query window. You add the Pen table, the Bird table, and the Bird-Pen Int. table. You select the Date, Bird#, Pen# from the Int. table. You select the tx#, the Bird name, or whatever from the other tables. You save the view. This view shows everything you need to see. That's it. All you ever need to do is enter in the date, bird# and pen#. Maybe you would have a bird weight field in the Int. table if the weight changes. But if that is the case, its just like a date. Dates can change, and weight can change.

Do you get this concept at all? If not, when do you need this done by, and if not, give me your email address, and I'll email you, I'll send you some pictures of what it looks like.