query match

  • Thread starter Thread starter David M. Fizer
  • Start date Start date
D

David M. Fizer

Hello,


I want to have a query ask the user to enter in a primary key of a record
then display all records that match the certain fields of the record they
have entered. I know how to have the query ask for a record I just need
help on matching the certain fields.




UPC Caps Container Tray

111 PK123 PK231 PK301
222 PK321 PK221 PK331
333 PK123 PK231 PK341


If the user entered in 111 and if the number in the caps and container
field matched then the results would show the 333 record.


Thanks,
David
 
David M. Fizer said:
Hello,


I want to have a query ask the user to enter in a primary key of a
record then display all records that match the certain fields of the
record they have entered. I know how to have the query ask for a
record I just need help on matching the certain fields.




UPC Caps Container Tray

111 PK123 PK231 PK301
222 PK321 PK221 PK331
333 PK123 PK231 PK341


If the user entered in 111 and if the number in the caps and container
field matched then the results would show the 333 record.
Click to expand...

I haven't tested this, but I think it would be something along the lines
of

SELECT
Products.*
FROM
Products
INNER JOIN
Products AS T
ON
(Products.Caps = T.Caps) AND
(Products.Container = T.Container)
WHERE
(T.UPC=[Enter UPC:]) AND
(Products.UPC<>[Enter UPC:]);

In this example, I've chosen "Products" as the name of the table, and
I'm assuming that you don't want to see the record that you're choosing
to match against; only the other records that match it on those two
fields.
 
Hi Dirk,

Thank you for replying. Could you be a little more specific as I am fairly
new to Access.On Sat, 15 Oct 2005 22:01:43 -0400, Dirk Goldgar
David M. Fizer said:
Hello,


I want to have a query ask the user to enter in a primary key of a
record then display all records that match the certain fields of the
record they have entered. I know how to have the query ask for a
record I just need help on matching the certain fields.




UPC Caps Container Tray

111 PK123 PK231 PK301
222 PK321 PK221 PK331
333 PK123 PK231 PK341


If the user entered in 111 and if the number in the caps and container
field matched then the results would show the 333 record.
Click to expand...

I haven't tested this, but I think it would be something along the lines
of

SELECT
Products.*
FROM
Products
INNER JOIN
Products AS T
ON
(Products.Caps = T.Caps) AND
(Products.Container = T.Container)
WHERE
(T.UPC=[Enter UPC:]) AND
(Products.UPC<>[Enter UPC:]);

In this example, I've chosen "Products" as the name of the table, and
I'm assuming that you don't want to see the record that you're choosing
to match against; only the other records that match it on those two
fields.
Click to expand...
 
David M. Fizer said:
Hi Dirk,

Thank you for replying. Could you be a little more specific as I am
fairly new to Access.On Sat, 15 Oct 2005 22:01:43 -0400, Dirk Goldgar
David M. Fizer said:
Hello,


I want to have a query ask the user to enter in a primary key of a
record then display all records that match the certain fields of the
record they have entered. I know how to have the query ask for a
record I just need help on matching the certain fields.




UPC Caps Container Tray

111 PK123 PK231 PK301
222 PK321 PK221 PK331
333 PK123 PK231 PK341


If the user entered in 111 and if the number in the caps and
container field matched then the results would show the 333 record.
Click to expand...

I haven't tested this, but I think it would be something along the
lines of

SELECT
Products.*
FROM
Products
INNER JOIN
Products AS T
ON
(Products.Caps = T.Caps) AND
(Products.Container = T.Container)
WHERE
(T.UPC=[Enter UPC:]) AND
(Products.UPC<>[Enter UPC:]);

In this example, I've chosen "Products" as the name of the table, and
I'm assuming that you don't want to see the record that you're
choosing to match against; only the other records that match it on
those two fields.
Click to expand...
Click to expand...

I don't see how I could be more specific unless you tell me the name of
your table. But if you take the SQL statemnt I posted, modify it so
that the name of your table is substituted wherever I wrote "Products",
and then paste the resulting statement into a new query opened in SQL
View, that query (barring other minor syntactical errors) ought to give
you the records you want when you switch it to Datasheet View.
 
Dirk YOU ARE THE MAN Thankyou it worked Great.



David M. Fizer said:
Hi Dirk,

Thank you for replying. Could you be a little more specific as I am
fairly new to Access.On Sat, 15 Oct 2005 22:01:43 -0400, Dirk Goldgar
Hello,


I want to have a query ask the user to enter in a primary key of a
record then display all records that match the certain fields of the
record they have entered. I know how to have the query ask for a
record I just need help on matching the certain fields.




UPC Caps Container Tray

111 PK123 PK231 PK301
222 PK321 PK221 PK331
333 PK123 PK231 PK341


If the user entered in 111 and if the number in the caps and
container field matched then the results would show the 333 record.

I haven't tested this, but I think it would be something along the
lines of

SELECT
Products.*
FROM
Products
INNER JOIN
Products AS T
ON
(Products.Caps = T.Caps) AND
(Products.Container = T.Container)
WHERE
(T.UPC=[Enter UPC:]) AND
(Products.UPC<>[Enter UPC:]);

In this example, I've chosen "Products" as the name of the table, and
I'm assuming that you don't want to see the record that you're
choosing to match against; only the other records that match it on
those two fields.
Click to expand...
Click to expand...

I don't see how I could be more specific unless you tell me the name of
your table. But if you take the SQL statemnt I posted, modify it so
that the name of your table is substituted wherever I wrote "Products",
and then paste the resulting statement into a new query opened in SQL
View, that query (barring other minor syntactical errors) ought to give
you the records you want when you switch it to Datasheet View.
Click to expand...
 
Back
Top