Combo Box and Relationship - giving query and report number not te

[domdog]

I have a table1 that fills out the name field thru the use of another
table2, a list of names that are given an ID number(primay key), using a
combo box on a form.
I use this table1 to fill out another table3.
Table3 is a rearrangement of table1. I do this only because the
form I use to fill out table one is a given and to facilitate data entry the
form on the computer must look a like the form that people fill out or they
get confused rearranging the data in their head while entering the data or
checking the data between to different layouts. The form has other benifits
because it is self checking in the field (w/o a computer).
There is no access relationships between table1 and table3 or Look up
fields. There is a one to many relationship between table2 and table 3 (
one name and ID to Many names in table3)
When I do a query based on table1 and table3 to pull out information I can
get the query to work but the Name field on table2 shows up correctly as
text(a name) but the Name field on table1 comes out as a number. This number
follows thru to any Reports I try to make. Since there is no relationship
between table1 and table2 except the combo box query I figure this is were
the problem is coming from. But the bottom line is that I would like to see
Names not numbers in my query and I definitly need to see Names in my reports.

 

[John Vinson]

I have a table1 that fills out the name field thru the use of another
table2, a list of names that are given an ID number(primay key), using a
combo box on a form.
I use this table1 to fill out another table3.
Table3 is a rearrangement of table1. I do this only because the
form I use to fill out table one is a given and to facilitate data entry the
form on the computer must look a like the form that people fill out or they
get confused rearranging the data in their head while entering the data or
checking the data between to different layouts. The form has other benifits
because it is self checking in the field (w/o a computer).

WHOA.

It is NOT appropriate to use two *TABLES* to do this. The arrangement
of fields on the screen has *absolutely nothing to do* with the
storage of data in the Table!

You can use a Form to lay out the fields in any order or arrangement
you want. Creating a new table to do this is simply neither necessary
nor appropriate!

There is no access relationships between table1 and table3 or Look up
fields. There is a one to many relationship between table2 and table 3 (
one name and ID to Many names in table3)
When I do a query based on table1 and table3 to pull out information I can
get the query to work but the Name field on table2 shows up correctly as
text(a name) but the Name field on table1 comes out as a number. This number
follows thru to any Reports I try to make. Since there is no relationship
between table1 and table2 except the combo box query I figure this is were
the problem is coming from. But the bottom line is that I would like to see
Names not numbers in my query and I definitly need to see Names in my reports.

Then you need to normalize your data structure, and create a
relationship between the tables. Your problem is not in the Report -
it's in your table design. Fix that and your Report should be very
straightforward, using a Query linking the two tables.

John W. Vinson[MVP]

 

[domdog]

Thank You for the Re.....much appreciated.

Fix that and your Report should be very

straightforward, using a Query linking the two tables.

How do I do this? I use Relationships but this I don't understand.



WHOA.

It is NOT appropriate to use two *TABLES* to do this. The arrangement
of fields on the screen has *absolutely nothing to do* with the
storage of data in the Table!

??? I can have a single form that adds multiple records to a single table??
??? I can have a single form that adds the colums and rows (addition is done
on the paper form and needs to be check by computer on data entry, I do not
store these calculations but the user is able to futher check that the paper
form was filled out correctlly and/or that data entry was done correctly???

You can use a Form to lay out the fields in any order or arrangement
you want. Creating a new table to do this is simply neither necessary
nor appropriate!

Well I thought this would be part of your answer. Maybe I'm just confused
on this matter but.... My Form is built off an existing paper form that
can't be changed. The problem is that this paper form has multiple Records
of one table on it. It would be better to be on an Excell sheet but the
tools for creating a form and handling the data once collected are easier and
quicker in Access. The records are analyzed and collected on the form both
vertically and horizontially and within the rows and colums there are
relationships That need to be collected and brought out then analyzed(after
collection). This is Excell stuff but when I read the design choice
documents I'm right on the cusp of the two programs and Access wins out.
My database at this point works very well, those that have seen it are very
happy and are cofident that because of the form layout there will be many
less errors in data entry, which has been the problem. The Reports that I
have now have no problems and equal those of the past and exceed them in some
ways.
Other attempts at solving this problem have had the records split off
the form and entered singly. This has lead to the problems in data entry.
Getting the data in and checked by the user then validated by the computer I
feel is the key to success. My form does this well. The problem is once the
computer gets a hold of it it's a little tougher for me to work with but
doable.
If I have any confusion it surrounds having a form that the rows and
colums are both added (for checking) and that within those rows and colums
are other relationships that need to be brough out(but not at the time of
entry). Maybe I'm wrong when I feel that the form function is not as strong
in Excell but when I look at the documentation on line I don't see a easy
path to get what I want done both visually and functionally(I'm using VBA to
get part of the validation done). I have built forms, altered them, in
Excell and found it to be slow and with poor results.
Evey thing I have done in building this database has worked well to the
end result!!
The only problem I have is that with one query I use. The results are
accrate and what I need but when I put that query to a report I get the
numbers ( the correct numbers) but the those numbers equal names and the
names need to be printed not the associated number. Maybe this linking
query is the answer.