By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,760 Members | 1,202 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,760 IT Pros & Developers. It's quick & easy.

Problem with Queries and Cartesian Product Joins

P: n/a
Isn't life a bitch! You know what you want but you don't know how to
get it.

I have produced 12 queries that calculate a payment profile over 12
months. For a number of the records (ie with the same product
id)there are likely to be more than one payment recorded.
Nonetheless, when I run the queries individually and the query relates
to a table that provides referential integrity through a one to many
relationship, the query does what it's meant to and shows all the
payments per month. There are a number of time based functions that
make it a little more complex (ie if, and or or statements)but I seem
to have dealt with those fine.

Now comes the problematical part. I thought it would be easy to
absorb/join all the queries into a single new query that would neatly
show me the payment profile for each record/row on a month by month
basis. How naive I am!!

I've been unable to correctly join the individual queries and I think
I've produced what's called a cartesian or product join and I'm seeing
every possible combination of the 12 monthly payments. I've tried
every combination of inner and outer join without success.
Occasionally I get a message advising me to do some initial joining
through an SQL statement but this would take me into unknown
territory.

I think I understand the problem but don't know how to overcome it.
I'm at the limit of my Access knowledge with no where to turn. In
fact I'm wondering whether I'm attempting a bridge too far and should
simply give up and forget it?

Any help would be gratefully received if there is a reasonably easy
way to overcome the problem. I have Access 2003 All in ONe Desk Top
Reference for Dummies but this doesn't go this deep and Access Help is
pretty vague.

Any help or suggestions would be gratefully received.

Thanks
John
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
John,

When problems like this pop-up, many times it is the result of how the tables
are designed. I'd gamble that this is the cause of your problems. My suggestion
is for you to post your tables for the frequent responders to look at and maybe
make recommendations.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"John Smith" <jo********@hotmail.com> wrote in message
news:67**************************@posting.google.c om...
Isn't life a bitch! You know what you want but you don't know how to
get it.

I have produced 12 queries that calculate a payment profile over 12
months. For a number of the records (ie with the same product
id)there are likely to be more than one payment recorded.
Nonetheless, when I run the queries individually and the query relates
to a table that provides referential integrity through a one to many
relationship, the query does what it's meant to and shows all the
payments per month. There are a number of time based functions that
make it a little more complex (ie if, and or or statements)but I seem
to have dealt with those fine.

Now comes the problematical part. I thought it would be easy to
absorb/join all the queries into a single new query that would neatly
show me the payment profile for each record/row on a month by month
basis. How naive I am!!

I've been unable to correctly join the individual queries and I think
I've produced what's called a cartesian or product join and I'm seeing
every possible combination of the 12 monthly payments. I've tried
every combination of inner and outer join without success.
Occasionally I get a message advising me to do some initial joining
through an SQL statement but this would take me into unknown
territory.

I think I understand the problem but don't know how to overcome it.
I'm at the limit of my Access knowledge with no where to turn. In
fact I'm wondering whether I'm attempting a bridge too far and should
simply give up and forget it?

Any help would be gratefully received if there is a reasonably easy
way to overcome the problem. I have Access 2003 All in ONe Desk Top
Reference for Dummies but this doesn't go this deep and Access Help is
pretty vague.

Any help or suggestions would be gratefully received.

Thanks
John

Nov 13 '05 #2

P: n/a
Thanks,
How do I do this?
John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

P: n/a
You would only need to post the pertinent tables and only the pertinent fields
in the tables. Usually it is done here like this:

TblMytableA
MyTableAID
FieldA1
FieldA2
etc

TblMyTableB
MyTableGID
FieldB1
FieldB2
etc

Steve
PC Datasheet
"John Smith" <jo********@hotmail.com> wrote in message
news:40**********************@news.newsgroups.ws.. .
Thanks,
How do I do this?
John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #4

P: n/a
"PC Datesheet" <no****@nospam.spam> wrote in message news:<By*****************@newsread2.news.atl.earth link.net>...
You would only need to post the pertinent tables and only the pertinent fields
in the tables. Usually it is done here like this:

TblMytableA
MyTableAID
FieldA1
FieldA2
etc

TblMyTableB
MyTableGID
FieldB1
FieldB2
etc

Steve
PC Datasheet
"John Smith" <jo********@hotmail.com> wrote in message
news:40**********************@news.newsgroups.ws.. .
Thanks,
How do I do this?
John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Hi Steve,
Each query is made as follows

tbl Accommodation
tbl Interests
tbl Total Accommodation Sq Ft

They are linked as follows:

tbl Accommdation > tbl Interests One to Many Relationship through a
Property ID ((Site No).
tbl Accommodation > tbl Accommodation Sq Ft One to One. The purpose
of the latter table is simply to provide a total sq ft for each
property, the property ID (site no) being the link.
The query is then constructed as follows:

Site No (tbl Accommodation)- a unique reference for the property.
Property Name (tbl Accommodation)- what the property is called.
Sq Ft (tbl Accommodation) - how big is the area. There can be more
than one record per site no but the aggregate of the records cannot
exceed the total for the whole building.
Vacancy Date (tbl Accommodation) - when did it become vacant.
Area % (This is a an expression calculating the % of accommodation for
the record as against the whole building.
Month. This is a date ie, 1/1/04 and the idea is to create a separate
query for each month whereby this month would change consecutively eg,
1/2/04, 1/3/04 etc
Next Review (tbl Interests) What is the date upon which the rent is
reviewed.
RRD
VD
DBED
These three cells return 0, -1 (true, false) dependent upon certain
dates that impact whether a charge is made.
Rent (tbl Interests) This is the total rent for the Property
Expression 1 This is the key to the query. It calculates whether a
charge is relevant to that month in question. I have copied the
expression; you won't understand it all but it will give you an
indication of what it's trying to achieve

Expr1: IIf([DBED]=0 Or [VD]=0 Or [Area %] Is Null,0,IIf([RRD]=-1 And
[DBED]=-1 And [VD]=-1 Or [Estimated Rent Review] Is Null,[Rent]*[Area
%]/12,IIf([RRD]=0 And [DBED]=-1 And [VD]=-1 Or [VD] Is Null,[Estimated
Rent Review]*[Area %]/12,[Rent]*[Area %]/12)))

Estimated Rent Review (tbl Interests) If the rent increases during
the period, to what value.
End Date - This is an expression that calculates which of the earlier
of three dates does the agreement end on.
Disposal Date (tbl Interests)
Break Dates (tbl Interests)
Lease End Date (tbl Interests)

These are the three dates that feed into the End Date.

If you follow all of this then I take my hat off to you. I am 99%
happy that this query works. I have tested various scenarios using
different variables and Expr 1 appears to return the right monthly
value for the various records in the database.

The problem appears when I try and combine this query in a query that
draws together identical queries for each month, ie Jan, Feb, Mar, Apr
etc. The queries are exactly the same with the exception of the Month
where the date changes. I was hoping that the query would combine in
order that I could consolidate each monthly charge in a single
location ie,

Expr1 Jan
Expr1 Feb
Expr1 Mar
Expr1 Apr and so on.

However it is here that I have had problems with getting the right
join. It would seem that I cannot create a one to many relationship
of enforce referential integrity and a cartesian product join.

Hope this all makes sense and thanks for your help.

John
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.