new to relational databases in access 2003 and forms

[nleach]

I have what I think is a fairly simple idea but can't seem to find the solution.

we have a table1 filled with idnumber, name, address info.

we have a 2nd table that's empty.

data entry person should open up form, type in an idnumber (that will match up to the idnumber on table1) and it will populate table2's fields with all the info table1 has in it.

everything i have found, as far as tutorials etc, has both tables already containing the id numbers, address fields, etc.

basically table2 starts out empty and when you type in "00001" or "001231", it populates that records fields with "00001" or "001231"'s corresponding data from table1.

I'm sure this is simple to do, I just cant find how to do it or even what terminology to search for.

Thanks in advance for any help.

 

[CEH]

data entry person should open up form, type in an idnumber (that will match up to the idnumber on table1) and it will populate table2's fields with all the info table1 has in it.

And the purpose for this is????

Do a search on normalization.

 

[nleach]

And the purpose for this is????

Do a search on normalization.

the purpose is for mail that's returning.

to track donations, responses, things of that nature.

we have the entire file we mailed, which will be the matchback table. we'll be building a new table, using the idnumber from the response on the mailpiece, and logging what they responded with.

if that makes sense. i'll try that normalization search.

thanks!

 

[unclejoe]

Hi,

This is what we called one to many relationships.

For example, you have the Donations Table and Donors Table.

Donors contained the Idnumber, donorName, adresss etc.

Donations contained the idnumber, donateAmount, DonateDate.

Donors table have only one unique idnumber where as Donations have many idnumber because one donor may contribute more than once.

Once you created the tables, go to Tools – Relationships, Show Table – Tables Tab, Select the Tables, click ok.

Click and drag the Donors.idnumber to Donations.idnumber, a Edit Relationship box appears, check the box for Enforce Referential Integrity, click Create and you’re done.

Next create a Form with the RecordSource from Donors Table. In the same form, create a subform and with the RecordSource from the Donations Table.

Once that is done, you’ll notice whenever you insert a new record the idnumber of the Main Form matches the Subform idnumber. (you may need to disable the control for innumber in the subform or hide it)

You do not need to populate whatever fields from Donors to Donations Table.

I have what I think is a fairly simple idea but can't seem to find the solution.

we have a table1 filled with idnumber, name, address info.

we have a 2nd table that's empty.

data entry person should open up form, type in an idnumber (that will match up to the idnumber on table1) and it will populate table2's fields with all the info table1 has in it.

everything i have found, as far as tutorials etc, has both tables already containing the id numbers, address fields, etc.

basically table2 starts out empty and when you type in "00001" or "001231", it populates that records fields with "00001" or "001231"'s corresponding data from table1.

I'm sure this is simple to do, I just cant find how to do it or even what terminology to search for.

Thanks in advance for any help.

 

[nleach]

Hi,

This is what we called one to many relationships.

For example, you have the Donations Table and Donors Table.

Donors contained the Idnumber, donorName, adresss etc.

Donations contained the idnumber, donateAmount, DonateDate.

Donors table have only one unique idnumber where as Donations have many idnumber because one donor may contribute more than once.

Once you created the tables, go to Tools – Relationships, Show Table – Tables Tab, Select the Tables, click ok.

Click and drag the Donors.idnumber to Donations.idnumber, a Edit Relationship box appears, check the box for Enforce Referential Integrity, click Create and you’re done.

Next create a Form with the RecordSource from Donors Table. In the same form, create a subform and with the RecordSource from the Donations Table.

Once that is done, you’ll notice whenever you insert a new record the idnumber of the Main Form matches the Subform idnumber. (you may need to disable the control for innumber in the subform or hide it)

You do not need to populate whatever fields from Donors to Donations Table.

when i try and click "Enforce referential integrity", i get the message "no unique index found for the referenced field of the primary table"

i have "donor information" table and a "responses" table. Responses table is empty. Can I not use referential integrity if the fields are empty?

When I put "00001" into the USERID field of the form for Responses, I'd like it to populate the other fields with information from the record in "donor information" table that has "00001" as it's USERID.

 

[Mile-O]

Can you detail the tables you have, the fields within them, and their data types? Highlight on which fields you are trying to join.

It becomes less of a guessing game when the details necessary to work with are presented.

 

[nleach]

sure, i'll try to make it as simple as i can. I dont think i'm explaining it right.

table: mailed
fields: contactid, name, keycode

table: responses
fields: contactid, name, keycode, dateresponsed, amount

The form will show the RESPONSES fields.

so if i have
table: mailed
record: 001, John Smith, ABCDEFG

data entry person picks up a piece of mail and somewhere on there is printed the contactid.

He types into the first field of the form, contactid, "001" and hits tab.
when he hits tab, keycode and name field should populate with "john smith" and "ABCDEFG" that are pulled from the MAILED table. Then data entry person updates the "dateresponded" and the "amount" field with whatever.

he types in

 

[nleach]

any ideas?

 

[CEH]

At this point you have multiple tables with the same information. Not a normalized DB........ And I still see no point. The questions you want answered on the form are has it been mailed? Use a checkbox field... "Mailed" Yes/No. Has it had a respose? another field for "Response" Yes/No. Add "Date responded" and "Amount"
But..... If you really want to, and see the need, break it done as far as you like. But there is no need (and it will cause you problems) if you duplicate fields in seperate tables.

 

[nleach]

At this point you have multiple tables with the same information. Not a normalized DB........ And I still see no point. The questions you want answered on the form are has it been mailed? Use a checkbox field... "Mailed" Yes/No. Has it had a respose? another field for "Response" Yes/No. Add "Date responded" and "Amount"
But..... If you really want to, and see the need, break it done as far as you like. But there is no need (and it will cause you problems) if you duplicate fields in seperate tables.

the whole point is to save time on data entry.

data entry person keys in the ID number and that record pops up so that they can then enter the "response date" and "amount" information to the proper record without entering in the entire record and possibly miskeying anything.

 

[Mark-BES]

Take my advise - (I had to learn the hard way).

Spend some time looking at the sample database's shipped with Access 03.
Look at how they work both to the user and behind the scenes - this WILL take more than 5 minutes.

Do not start designing in access yet. Sit down with a pen and paper and work out the processes.

Next, start building you tables piece by piece and use this site for reference. Use the search function before posting questions as most of the topics you will be asking about will have been covered many, many times.


I am but a mere database novice and am still learning however, the pro's who regularly comment on here really are worth their weight in gold. Listen to them, they know what they are talking about.

 

[unclejoe]

Hi,

The "mailed" table, go to the Table Design mode - go to the ContactID, at the General Tab, at the Indexed Field, change to "Yes (No Dulplicates)".

But on the "responses" table, the "contactid" column - it must be set to "Yes (Dulplicates OK)".

However, these two columns must be unique in order for the relationship to work.

The table "mailed" must not have multiple same "contactid".
The table "responses" can have multiple "contactid".

As it is impossible for us to know how you design your database, goHere

to read about them.

PS. The Column name "KeyCode" is a reserve name in Access, do use it, rename it to something else.

when i try and click "Enforce referential integrity", i get the message "no unique index found for the referenced field of the primary table"

i have "donor information" table and a "responses" table. Responses table is empty. Can I not use referential integrity if the fields are empty?

When I put "00001" into the USERID field of the form for Responses, I'd like it to populate the other fields with information from the record in "donor information" table that has "00001" as it's USERID.

 

[Mile-O]

the whole point is to save time on data entry.

I fail to see how saving time on data entry is affected by getting a robust structure in place.

What you are suggesting is easily done with a relational database. You just need to take the time to understand how it is done before you get tangled in your own web.

 

[nleach]

I fail to see how saving time on data entry is affected by getting a robust structure in place.

What you are suggesting is easily done with a relational database. You just need to take the time to understand how it is done before you get tangled in your own web.

i thought i understood how it is done, two tables connected via an idnumber field.

or perhaps one table that has an idnumber field and the dataentry person then keys in some IDnumber and it brings up that idnumber's other fields to be edited, viewed, etc.

 

[CEH]

i thought i understood how it is done, two tables connected via an idnumber field.

That is correct. But not two tables with the same fields in them. Do your two tables, connected on your ID field. Do a query of these two tables and build your form. I think you'll see it works rather well when set up correctly.

 

[nleach]

Take my advise - (I had to learn the hard way).

Spend some time looking at the sample database's shipped with Access 03.
Look at how they work both to the user and behind the scenes - this WILL take more than 5 minutes.

Do not start designing in access yet. Sit down with a pen and paper and work out the processes.

Next, start building you tables piece by piece and use this site for reference. Use the search function before posting questions as most of the topics you will be asking about will have been covered many, many times.


I am but a mere database novice and am still learning however, the pro's who regularly comment on here really are worth their weight in gold. Listen to them, they know what they are talking about.

yea I hear you about the search, i'm just not sure exactly the terms i should be searching on.

I understand all about relational databases. THe problem I'm finding is that all the tutorials and information I find on two linked tables in a database has both tables containing the same contactid and other fields that are not the same.

table1 = contactid, name, address
table2 = contactid, donation, shoe size

both tables are populated.

i need 1 table with contactid, name, address
table2 is EMPTY but has fields for contactid, name, address, AND donation

i could use a copy of table1 with extra "donation" field but i dont want record 1 showing up as soon as data entry opens the table.

i'd like them to key in a contactid or finder number and then the table "searches" for the right record and shows only that one?

is that easier?

 

[nleach]

after thinking about it more, we definatly need two tables.

one with idnumber, name, address info
2nd with idnumber, gift amount, date information.

data entry will provide idnumber to 2nd table, name will then be shown on the form after the idnumber is matched back to Table1 (for QC'ing purposes) and then data entry can put in the gift amount and date information.

i just dont know how to get it to search and display information from table1 on a form that's designed for putting data into table2.

 

[unclejoe]

i just dont know how to get it to search and display information from table1 on a form that's designed for putting data into table2.

Hi,

First, Create a Main Form with the RecordSource from Table1.
Second, in that same form, create a sub form where the Recordsource is from Table2.

Is that easy.

 

[Mile-O]

Looks more like you need two tables. One for contacts and one for donations.

A structure like this:

tblContacts
ContactID (Autonumber)
ContactForename (Text)
ContactSurname (Text)
Address1 (Text)
Address2 (Text)
Telephone (Text)

tblDonations
DonationID (Autonumber)
ContactID (Number)
DonationAmount (Currency)

The two fields called ContactID are related now in a one to many relationship.

The way you had it was wrong in that both your tables seemed to contain ContactID as a primary key. Only the contacts table should have this, the other one should be a table modelled on donations.

At one point you said the following:

I understand all about relational databases.

Based on your suggested structure, you don't. The one above will give you what you need. On a form, selecting the ContactID is all the data entry you will ever need. You can simulate details being filled in using comboboxes bound the columns of a main combo with hidden columns (of which your ContactID is bound to a combo - on a form bound to a query based off the donations table)

 

[Mile-O]

And here is an example showing what I mean: