Relationships

[Lou]

Hi,

I have a database that contain records where one record may contain 10
different elements with up to 3 Elements. However the lst element of each is
the primary element and is the one I am trying to pull data on.

Rel ELE1 ELE2 ELE3
LIH ELE4 ELE4 ELE5
SS ELE6 ELE7 ELE8
etc.

I also have a table where I list the element number, element description,
error, and error description.

I tried to create a relationship between the two tables, but when I create a
query it only pulls cases with ELE1 only.

Can someone show me where I am going wrong. Hope I explained enough.

Thanks!

 

[Rick Brandt]

Hi,

I have a database that contain records where one record may contain 10
different elements with up to 3 Elements. However the lst element of each is
the primary element and is the one I am trying to pull data on.

Rel ELE1 ELE2 ELE3
LIH ELE4 ELE4 ELE5
SS ELE6 ELE7 ELE8
etc.

I also have a table where I list the element number, element description,
error, and error description.

I tried to create a relationship between the two tables, but when I create a
query it only pulls cases with ELE1 only.

Can someone show me where I am going wrong. Hope I explained enough.

Any time you have a table with fields like Something1, Something2, Something3,
etc., then you have a good design for a spreadsheet, but not for a database
table. Your table should be structured so that each element is on its own row
in a common field rather than using a field per element.

Rel ELE1 PRI
Rel ELE2
Rel ELE3
LIH ELE4 PRI
LIH ELE5
LIH ELE6
SS ELE7 PRI
SS ELE8
SS ELE9

 

[Amy Blankenship]

Once I set them in this manner how can I get the other table that list the
type/description of element and error to pull this information into a
report
with all the rows and not just the first row?

Assume your tables are like this

tblElements
Element
Desc

tblElementThings (this is the table that Rick Described)
ElementThing: This is whatever Rel, LIH, etc. stand for
ElementThingOrder: This is the 1, 2, 3 that were from the Ele1-Ele10 column
heads
Element: This is the actual element that is related to the "thing" in that
order. So this is the actual value that would have been in ELE1, etc.

Now selecting is easy. Open your query grid and show both these tables.
Drag Element from one table to the other, and you'll see a join line show
up. Drag all fields from both tables. Click the sort row for
ElementThingOrder and choose "Ascending". Run the query.

You can use this as the basis for a report.

HTH;

Amy