Query Help

[MTSPEER]

Hello,

I need some help with a query where I need to make sure that there are 3 records of the same number in field Field1 and that each set of 3 records has to have a D, an R, and an S in the field Field2. So it should look like this:

Field1 Field2
1111 D
1111 S
1111 R

1112 D
1112 S


1112 R

And there are over a 1000 records. I need a way to make sure that there are 3 of each number in Field1 and that each set has D, S, and R in Field2.

Thanks

[ranman256]

if you have 1 query, Q1, that shows only unique FIELD1 , 1111,1112
and
have 1 query, Q2, that shows only unique FIELD2, D,S,R

then make Q3 the has BOTH queries in the field BUT do not join them. (By not joining them it will multiply the 2 queries.)
bring down Q1.FIELD1, & Q2.FIELD2 onto the grid.


append to a table.

[MTSPEER]

That worked! now how can I make sure that both tables are the same? or if something is missing?

[MTSPEER]

I think you misunderstood what I'm trying to do, I need to validate the file that I imported that it has 3 of each number and D,S, and R for each of them. After making that other table is there a way to validate against the two tables?

[ranman256]

yes, use this FULL table to join to the main table
do an OUTER JOIN
to see whats missing in your data table.

[MTSPEER]

ah okay thank you sir. Also, can you show me how to create a query in the MainTable so there isnt a number having dublicates with Field2. for ex:

Field1 Field2
11111 D
11111 D
11111 S

[MTSPEER]

Nevermind I figured it out. thanks!