Help

[Arnie]

not very good at access

i have 6 tables with 93 fields in each. i have created forms to view each
table. But what i need is to be able to create a Query that can display all
tables on one form. the second field is "location" so i need the criteria to
be based on that filter.
i have tried to make a query but there are too many fields for all six tables

any help would be appreciated

Arnie

 

[John W. Vinson]

not very good at access

i have 6 tables with 93 fields in each.

Then your table structure is almost certainly WRONG. A table with 30 fields is
getting really wide; 93 is huge. And having six (presumably) identically
structured tables is simply *wrong*.

i have created forms to view each
table. But what i need is to be able to create a Query that can display all
tables on one form. the second field is "location" so i need the criteria to
be based on that filter.
i have tried to make a query but there are too many fields for all six tables

What data is represented by the tables? How are they related? What are some
representative fieldnames (don't list all 93, just some examples)? I very
strongly suspect that you're "committing spreadsheet" and have some one to
many relationships embedded in each record, and that a tall-thin normalized
design will be a better approach.

 

[Arnie]

John no the only field that has any relationship is "Location" as these are
all test results. I do need to be able to present the data filtered by
Location but keeping the type of results seperate. Not all 93 Fields Used as
depends on Type of Test but i have designed a form to view all of the test
results even if data is missing.
Hope this makes sense


Test Location ---> Field 93

Test 1 A
B
Test 2 A
B
Test 3 A
B
Test 4 A
B
Test 5 A
B
Test 6 A
B

 

[John W. Vinson]

John no the only field that has any relationship is "Location" as these are
all test results. I do need to be able to present the data filtered by
Location but keeping the type of results seperate. Not all 93 Fields Used as
depends on Type of Test but i have designed a form to view all of the test
results even if data is missing.
Hope this makes sense


Test Location ---> Field 93

Test 1 A
B
Test 2 A
B
Test 3 A
B
Test 4 A
B
Test 5 A
B
Test 6 A
B

That's what I suspected: you've fallen into the very common "questionnaire
trap", using Tests as fieldnames.

Think about this: what happens if Test 20 is deleted, and you need to add
tests 94, 95 and 96? You'll need to redesign your tables, all your forms, all
your queries, all your reports... ouch!

You have a classic many to many relationship: each Location can have many
Tests, and each Test can be at many Locations. The correct structure for this
has three tables:

Locations
LocationID <primary key>
<fields about the location as an entity in its own right>

Tests
TestNo <Primary Key>
Description <of the test>

Results
LocationID <link to Locations>
TestNo <link to Tests>
Result <whatever's now in your Test N field for this test and location>

This normalized structure will make your work MUCH easier. You can use a
"Normalizing Union Query" to populate the results table, post back or do a
Google Groups search for details.