Help needed with a Join SQL Query (Access 97)

[kbhat]

This posting concerns the following tables in the database that I am
working with ---- a product table and four sales table ---- one for
each quarter [Well, this may not be the best table design, but this is
what we have in place, and it will be quite an effort to get it
changed].

The product table has information such as product name, product code,
product description etc. The sales tables have information like
product code and quarterly sales volume. Now here's the important
stuff. If a product is not sold in a particular quarter, it DOES NOT
make it to the sales table for that quarter.

I want to design a query which will provide the following information:

Product Name:
1Q Sales Volume:
2Q Sales Volume
3Q Sales Volume
4Q Sales Volume
Yearly Sales Volume

Even though this sounds so simple, I am having the hardest time
designing the SQL query. This is because of what I mentioned above
--- viz. if a product is not sold in a particular quarter, it DOES NOT
make it to the sales table for that quarter. In that case the query
should show a value of 0 for that quarter.

The queries that I am designing are either yielding only those
products that reported sales in all the quarters or they are yielding
a cartesian product of all my table entries. Any help will be
appreciated.

Thanks,
Bhat

 

[Guest]

Hi Bhat,

[Well, this may not be the best table design, but this is
what we have in place, and it will be quite an effort to get it
changed].

I'm glad you recognized this fact. You are most certainly right that it is
an incorrect design.

In that case the query should show a value of 0 for that quarter.

That's going to be a tougher result to obtain, versus just not showing a
record.

The queries that I am designing are either yielding only those
products that reported sales in all the quarters...

It sounds like you have the default INNER JOIN between all tables. Have you
tried using OUTER JOIN's, where the join lines all point towards the various
sales tables? Try double-clicking on a join line. You should see a dialog
that allows you to change the Join Type.

... or they are yielding a cartesian product of all my table entries.

That sounds like you tried running the query after deleting a join.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

This posting concerns the following tables in the database that I am
working with ---- a product table and four sales table ---- one for
each quarter [Well, this may not be the best table design, but this is
what we have in place, and it will be quite an effort to get it
changed].

The product table has information such as product name, product code,
product description etc. The sales tables have information like
product code and quarterly sales volume. Now here's the important
stuff. If a product is not sold in a particular quarter, it DOES NOT
make it to the sales table for that quarter.

I want to design a query which will provide the following information:

Product Name:
1Q Sales Volume:
2Q Sales Volume
3Q Sales Volume
4Q Sales Volume
Yearly Sales Volume

Even though this sounds so simple, I am having the hardest time
designing the SQL query. This is because of what I mentioned above
--- viz. if a product is not sold in a particular quarter, it DOES NOT
make it to the sales table for that quarter. In that case the query
should show a value of 0 for that quarter.

The queries that I am designing are either yielding only those
products that reported sales in all the quarters or they are yielding
a cartesian product of all my table entries. Any help will be
appreciated.

Thanks,
Bhat

 

[Guest]

Here is a sample that I have created for you.

http://home.comcast.net/~tutorme2/samples/SalesOrders.zip


Hope this helps!

Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Bhat,

[Well, this may not be the best table design, but this is
what we have in place, and it will be quite an effort to get it
changed].

I'm glad you recognized this fact. You are most certainly right that it is
an incorrect design.

In that case the query should show a value of 0 for that quarter.

That's going to be a tougher result to obtain, versus just not showing a
record.

The queries that I am designing are either yielding only those
products that reported sales in all the quarters...

It sounds like you have the default INNER JOIN between all tables. Have you
tried using OUTER JOIN's, where the join lines all point towards the various
sales tables? Try double-clicking on a join line. You should see a dialog
that allows you to change the Join Type.

... or they are yielding a cartesian product of all my table entries.

That sounds like you tried running the query after deleting a join.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[kbhat]

Here is a sample that I have created for you.

http://home.comcast.net/~tutorme2/samples/SalesOrders.zip


Hope this helps!

Tom,

Thanks a zillion for this. Unfortunately I am unable to open your
file with Access 97. I would appreciate if you could kindly save your
file as an Access 97 file.

Regards,
Bhat

 

[Guest]

Hi Bhat,

Try it now:

http://home.comcast.net/~tutorme2/samples/SalesOrders.zip

I converted the file to Access 97 format, however, I currently do not have
Access 97 installed, so I have no way of testing it. The .zip file is 72 KB
and now contains both the 97 and 2000 versions. I forgot to mention it
earlier, but this sample includes data copied from the Northwind sample
database, in case you are curious. I used data for calendar year 1997,
eliminating the rest of the data for 1996 and 1998.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________