Cartesian Queries

The UK's most regular instructor-led training courses.
Training information: access courses in london · Courses in microsoft access · Microsoft Office VBA training

Resolved · Medium Priority · Version 2003

3 posts

ReplyMon 7 Sep 2009, 16:20Delegate Mike said...

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];

Version	Date	Location	Places available	Book	Next place rate (£)		List price
Access Advanced 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice	List price
2003	Thu 19 Aug 2010	Southwark	4		£240	£245	£245
2007	Thu 26 Aug 2010	Southwark	5		£240	£245	£245
2010	Fri 17 Sep 2010	Bloomsbury	9		£225	£230	£299
2003	Fri 1 Oct 2010	Bloomsbury	8		£218	£223	£245
2003	Mon 8 Nov 2010	Southwark	9		£162	£167	£245
2010	Thu 25 Nov 2010	Bloomsbury	10		£150	£155	£299
Full Schedule: See all 24 Access Advanced course dates. Bookings currently available until 30th September 2011.

825 posts

ReplyThu 10 Sep 2009, 10:07Trainer Stephen said...

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

3 posts

ReplyThu 10 Sep 2009, 10:33Delegate Mike said...

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?

825 posts

ReplyThu 17 Sep 2009, 14:20Trainer Stephen said...

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

Thu 24 Sep 2009: Automatically marked as resolved.

· Microsoft Access and Customer Surveys
· What's In A Name? Creating A Company Personnel List Using Microsoft Access
· It's so easy to Access the information you need
· How To Create A Combo Control On An Access Form
· Making The Transition To Access - Why You Might Need It

Please browse our web site to find out more about
access courses in london and other Microsoft training courses.

Access tip:

Deleting duplicate records from a table

You cannot delete records tables where there duplicate records. A way around this is to create a new table which wont hold the duplicates. and then deleting the old table.

1. Use a make-table query based on this table only. IMPORTANT - Ensure that you include all of the fields from your original table or you may loose data.

2. Open the query's property sheet by using VIEW, QUERY, PROPERTIES, and set the Unique Values property to Yes

3. Because you have selected the Unique Values to Yes when you run the query, Access creates a new table without duplicate records. You can now delete the old table and rename the new one.

View all Access hints and tips