Query SOS

[Guest]

Dear MS Access Wizards,

I have come across a challenging exercise for querying in MS Access: I am
trying to create a query in Access that will use the first four letters of a
value in the “Name†field of table “A†as a search criterion to query the
“Corporate Name†field in table “Zâ€. The query should then append the value
from the “Corporate Number†field in table Z to a field in the corresponding
record in table A. This query needs to run approximately 5,000 times for
that many distinct records in table “Aâ€. The query becomes a bit complicated
because there will often be several records in table Z that match a search
for the first four letters of the value of the Name field in table A. Access
should create a list of records that match from which the user would select
the best match. The query should then append the corporate number from the
matched records of table Z to table A then continue automatically to the next
record in table A (using the first four letters of the Name field for the
subsequent record in Table A as search criteria) and so on…

Any help is greatly appreciated! Thank you, thank you!

Sincerely,
Joel Sivertsen
(e-mail address removed)

 

[John Vinson]

Dear MS Access Wizards,

I have come across a challenging exercise for querying in MS Access: I am
trying to create a query in Access that will use the first four letters of a
value in the “Name” field of table “A” as a search criterion to query the
“Corporate Name” field in table “Z”. The query should then append the value
from the “Corporate Number” field in table Z to a field in the corresponding
record in table A. This query needs to run approximately 5,000 times for
that many distinct records in table “A”. The query becomes a bit complicated
because there will often be several records in table Z that match a search
for the first four letters of the value of the Name field in table A. Access
should create a list of records that match from which the user would select
the best match. The query should then append the corporate number from the
matched records of table Z to table A then continue automatically to the next
record in table A (using the first four letters of the Name field for the
subsequent record in Table A as search criteria) and so on…

Any help is greatly appreciated! Thank you, thank you!

You should be able to create a query joining Table A to Table Z using
an expression:

SELECT A.ThisField, A.Thatfield, Z.Thisfield, Z.Thatfield
FROM A INNER JOIN Z
ON Z.[Corporate Name] LIKE Left([A].[Name], 4) & "*"
ORDER BY <whatever makes sense>

This query needs to be run once, not 5000 times, as it will match
every record in A up to the corresponding records in Z.


John W. Vinson[MVP]