473,322 Members | 1,398 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Problem with Queries and Cartesian Product Joins

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
4 3533
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
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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: deancoo | last post by:
I need to do a Cartesian product, which is inherently expensive. Turns out, it's too expensive. I've dropped in that portion of my C++ code in hopes that someone with greater expertise with STL...
3
by: Gerry | last post by:
Hi, I am trying to convert a Powerbuilder program from Sybase SQLA to MySQL. The following select works fine with Sybase but I can't get MySQL to accept it. SELECT .... FROM clients LEFT...
7
by: Thomas R. Hummel | last post by:
Hello all, I am trying to write a query that compares a member's enrollment period with the products that their group has had during that period (all members belong to a group and the products...
3
by: Richard Coutts | last post by:
I have two queries each of which having a column that I want to sum, and then add the two sums together. When I sum one query or the other, I get reasonable numbers. But when I try to add the two...
8
by: Alfonso Esteban Gonzalez Sencion | last post by:
I am trying to use Access as a front end for extracting information from an Oracle database. I started using linked tables but I am getting a very curious behaviour. When I consult the linked...
44
by: Christoph Zwerschke | last post by:
In Python, it is possible to multiply a string with a number: >>> "hello"*3 'hellohellohello' However, you can't multiply a string with another string: >>> 'hello'*'world' Traceback (most...
78
by: wkehowski | last post by:
The python code below generates a cartesian product subject to any logical combination of wildcard exclusions. For example, suppose I want to generate a cartesian product S^n, n>=3, of that...
3
by: shawnews | last post by:
Ok...I'll first describe briefly what I've done. Working from a paper form with over 200 fields - broken into 10 areas, I created a database with 10 tables. I then created a form using those 10...
2
by: zfareed | last post by:
I have a program that creates two sets, one thru user interaction and the other with the use of an array. Can anyone help with coding for finding the cartesian product of the two sets; i.e a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.