Mike has attended:
Access VBA course
Cartesian Queries
Do you have any tips/tricks on speeding up Cartesian queries between two unrelated tables.
Example.
TblTransactions contains Cardnumber
TblCard contains the "range" (a Low number and High number) and CardType
SELECT tblTransactions.Cardnumber, tblCard.CardType
FROM tblTransactions, tblCard
WHERE tblTransactions.CardNumber Between [Low] And [High];
RE: Cartesian Queries
Hi Mike
Thank you for your question
A Cartesian query combines every row in the first table with every row in the second table, and consequently can take a considerable length of time to run. In addition the data is in many cases meaningless.
Can you clarify the purpose of your query as I might be able to suggest an alternative approach
Regards
Stephen
RE: Cartesian Queries
I have approximately 5 million card numbers in a table that I need to determine the card type. Visa supply a list containing a range of cardnumbers and the card type, eg Low 453978000 High 453978999 CardType "D", this table of ranges is approximately 600000 rows.
So my query is 5 million x 60,000 and as you can imagine takes a hell of a long time. Is there any way of speeding it up using recordsets or any other method?
RE: Cartesian Queries
Hi Mike
Sorry for the delay in getting back to you, I have been away from the office for a few days
One possibility would be to first write the contents of the 2 tables into 2 Arrays.
You could then write a procedure that goes through the card numbers array a row at a time, retrieves the card number and then cycles through the card type array until it finds a match. It then retrieves the card type.
The skills covered on the VBA course should be sufficient for you to do this. If however you require specific help we could provide a more formal consultancy service. If this is required, please do not hesitate to contact our enquiries team.
Regards
Stephen
