The UK's Number 1 for Microsoft Office Training

Sitemap

Bookmark page

Printable version

Course updates
due to adverse
weather conditions

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Cartesian Queries

	98.3 % recommend us
	More live stats...

Bookmark this page

Share this with a friend or colleague:

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Cartesian Queries

Resolved · Medium Priority · Version 2003

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

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

For upcoming training course dates see: Pricing & availability

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

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?

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.

Access articles

· How To Successfully Design An Access Database For A Paying Client
· How To Use The New Total Feature In Microsoft Access 2007/10
· Use Split Form View In Access For Simultaneous Views
· Guide To Adding A Text Box Form Control In Access 2010
· The Benefits of Certified Microsoft Training in Access

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

Access tip:

Calculating The Difference Between Dates

If you wish to calculate the time between two date fields, this can be done in a number of ways:

1. As a calculated field in a query
2. As a calculated control in a form or report
3. As a calculation in a VBA procedure.

The basic syntax to get the number of days between two dates is:

=[One Date Field] - [Another Date Field]

You can also use one of the following functions:

=Month([One Date Field] - [Another Date Field])
which calculates the number of months between the two fields

=Year([One Date Field] - [Another Date Field])
which calculates the number of years between the two fields.

Another function is the DateDiff() function.

It uses an argument to determine how the time interval is measured. For example:

=DateDiff("q",[One Date Field] - [Another Date Field])
returns the number of quarters between the two fields.

Other intervals that can be used in this expression are as follows:
"yyyy" - Years
"m" - Months
"d" - Days
"w" - Weekdays
"ww" - Weeks
"h" - Hours
"n" - Minutes
"s" - Seconds