Linked Table Questions

[Guest]

I am linking to a table that I am unable to change. The table has 90,000+
lines of data (if that makes sense).

A problem arises when the part undergoes multiple tests before it passes (up
to a max of 3 tests). I need only the data from the final test - either test
#1, #2 or #3. (The final data will be #1 test if the part passes on that
test). The commonality these tests have is the unique serial number for the
part.

I considered making a query and eliminating the duplicates - but there wer
45,000 duplicates - that would be real time consuming.

Your help is appreciated.

 

[tina]

well, if each test record (a single "line" in a table is a record) has a
field with the number 1 or 2 or 3 to designate which test it is, and if each
record has a field containing the serial number of a specific part, then you
could build a Totals query, grouped on the serial number field, pulling the
Max of the test number field. a Totals query will not delete any records,
but should give you the dataset you want; you can always turn it into a
Make-Table query if you want to create a separate table of just those
records that you want to work with.

hth

 

[John Vinson]

I am linking to a table that I am unable to change. The table has 90,000+
lines of data (if that makes sense).

A problem arises when the part undergoes multiple tests before it passes (up
to a max of 3 tests). I need only the data from the final test - either test
#1, #2 or #3. (The final data will be #1 test if the part passes on that
test). The commonality these tests have is the unique serial number for the
part.

I considered making a query and eliminating the duplicates - but there wer
45,000 duplicates - that would be real time consuming.

Yah, if your computer is slow that might take upwards of thirty
seconds or so... <g>

You can create a Query selecting only the data from the highest test
value. Use a criterion on the test-number field of

=DMax("[TestNumber]", "[tablename]", "[SerialNumber] = '" &
[SerialNumber] & "'")

This assumes fieldnames and tablename, and that the SerialNumber field
is of Text datatype - leave off the ' and the ending & "'" if the
Serialnumber is in fact a Number field.

John W. Vinson[MVP]

 

[Guest]

I gain so much knowledge using this discussion group. -- please describe the
'30 second' means to eliminate the duplicates. and what is <g>?

"I considered making a query and eliminating the duplicates - but there were

45,000 duplicates - that would be real time consuming. "

And your response: "Yah, if your computer is slow that might take upwards of
thirty
seconds or so... <g>"

Thanks, Glenda

I am linking to a table that I am unable to change. The table has 90,000+
lines of data (if that makes sense).

A problem arises when the part undergoes multiple tests before it passes (up
to a max of 3 tests). I need only the data from the final test - either test
#1, #2 or #3. (The final data will be #1 test if the part passes on that
test). The commonality these tests have is the unique serial number for the
part.

I considered making a query and eliminating the duplicates - but there wer
45,000 duplicates - that would be real time consuming.

Yah, if your computer is slow that might take upwards of thirty
seconds or so... <g>

You can create a Query selecting only the data from the highest test
value. Use a criterion on the test-number field of

=DMax("[TestNumber]", "[tablename]", "[SerialNumber] = '" &
[SerialNumber] & "'")

This assumes fieldnames and tablename, and that the SerialNumber field
is of Text datatype - leave off the ' and the ending & "'" if the
Serialnumber is in fact a Number field.

John W. Vinson[MVP]

 

[Guest]

I entered:
=DMax("[TEST_NO]","[dbo_D_K_WETTEST]","[SERIAL_NO], = '" & [SERIAL_NO] & "'")

I receive an error message when I change the view "Syntax error (comma) in
query expression '[Serial_NO], = 'KM8500001-5001".

Thanks again for your help, Glenda

I am linking to a table that I am unable to change. The table has 90,000+
lines of data (if that makes sense).

A problem arises when the part undergoes multiple tests before it passes (up
to a max of 3 tests). I need only the data from the final test - either test
#1, #2 or #3. (The final data will be #1 test if the part passes on that
test). The commonality these tests have is the unique serial number for the
part.

I considered making a query and eliminating the duplicates - but there wer
45,000 duplicates - that would be real time consuming.

Yah, if your computer is slow that might take upwards of thirty
seconds or so... <g>

You can create a Query selecting only the data from the highest test
value. Use a criterion on the test-number field of

=DMax("[TestNumber]", "[tablename]", "[SerialNumber] = '" &
[SerialNumber] & "'")

This assumes fieldnames and tablename, and that the SerialNumber field
is of Text datatype - leave off the ' and the ending & "'" if the
Serialnumber is in fact a Number field.

John W. Vinson[MVP]

 

[John Vinson]

I entered:
=DMax("[TEST_NO]","[dbo_D_K_WETTEST]","[SERIAL_NO], = '" & [SERIAL_NO] & "'")

I receive an error message when I change the view "Syntax error (comma) in
query expression '[Serial_NO], = 'KM8500001-5001".

An extra comma sneaked in. Try

DMax("[TEST_NO]","[dbo_D_K_WETTEST]","[SERIAL_NO] = '" & [SERIAL_NO] &
"'")

As for the dups - copy and paste the table to a new table, design view
only. Change the query with the max values to an Append query into
this new table. Run it.

John W. Vinson[MVP]

 

[Guest]

Works GREAT -- thanks again
--
Glenda

I entered:
=DMax("[TEST_NO]","[dbo_D_K_WETTEST]","[SERIAL_NO], = '" & [SERIAL_NO] & "'")

I receive an error message when I change the view "Syntax error (comma) in
query expression '[Serial_NO], = 'KM8500001-5001".

An extra comma sneaked in. Try

DMax("[TEST_NO]","[dbo_D_K_WETTEST]","[SERIAL_NO] = '" & [SERIAL_NO] &
"'")

As for the dups - copy and paste the table to a new table, design view
only. Change the query with the max values to an Append query into
this new table. Run it.

John W. Vinson[MVP]

 

[Guest]

Sorry this has taken so long, I thought the solution by John would work, but
it is really slow. Could you explain your suggestion in more detail?

 

[Guest]

I got the last post to work but the data crunching is real slow. I tried
limiting the data to only one month (with a sub query) and it still takes so
long that the green status bar comes up. Any suggestions?
--
Glenda

I entered:
=DMax("[TEST_NO]","[dbo_D_K_WETTEST]","[SERIAL_NO], = '" & [SERIAL_NO] & "'")

I receive an error message when I change the view "Syntax error (comma) in
query expression '[Serial_NO], = 'KM8500001-5001".

An extra comma sneaked in. Try

DMax("[TEST_NO]","[dbo_D_K_WETTEST]","[SERIAL_NO] = '" & [SERIAL_NO] &
"'")

As for the dups - copy and paste the table to a new table, design view
only. Change the query with the max values to an Append query into
this new table. Run it.

John W. Vinson[MVP]

 

[John Vinson]

I got the last post to work but the data crunching is real slow. I tried
limiting the data to only one month (with a sub query) and it still takes so
long that the green status bar comes up. Any suggestions?

Any chance you could create a SQL/Server View? or write a Pass-Through
query that could have the SQL engine do the crunching? You would need
a correlated Subquery rather than the DMax(), which is a local Access
function.

For that matter, a Subquery

WHERE Datefield = (SELECT Max([datefield] FROM...)

might be more efficient even without a passthrough.

John W. Vinson[MVP]

 

[tina]

if you haven't already, suggest you try John's additional suggestion posted
1/30, elsewhere in this thread. his answer is likely the better approach. if
you still want to try my suggestion afterward (i doubt you'll need it), post
back.

hth