Solved

Complex Sql query

Posted on 2013-06-17
23
271 Views
Last Modified: 2013-06-19
Not sure if this is even possible.  I have two ms-access tables that I access using VB and Jet.  Table "A" is a list of products with pricing.  Table "B" has a list of items in table "A" that need the price added to a table "A" item to get a sales price.  For example table "A" has the following items:
ID      Item                         Price
1.  Bathtub                     150.00
2.  Install Bath Tub            50.00
3.  Bath Tub Faucet         100.00

Table B has the following entries
BaseItemId         ItemID
1                            2
1                            3

I would like a sql statement that selects Item 1 from table "A" then looks in table "B" for items with the matching baseItemID and gets the prices from table "A"  sums it and adds it to the Table "A" price.    In this case A.ID is the base item so the sales price would be 300.

I've spent several hours trying to figure it out.

Thanks
0
Comment
Question by:Moed
  • 10
  • 7
  • 3
  • +1
23 Comments
 
LVL 84
Comment Utility
I haven't seen your entire database, but it seems your data isn't stored correctly. You have a list of "items" that are associated with one or more "packages", so you'd need something like this:

tPackage
============
PackageID
PackageName

tPackageItems
================
PackageItemID
PackageID [foreign key to tPackage]
ItemName
ItemCost
etc etc

So a record in tPackage might look like this:

PackageID     PackageName
1             BathInstall

Open in new window

And a record in tPackageItems would look like this:

PackageItemID  PackageID  ItemName  ItemCost
1               1          BathTub      150
2               1          Install Tub  100
3               1          Faucet        50

Open in new window

With a structure like this, you can then use SQL to easily get your "Package" cost:

SELECT SUM(ItemCost) FROM tPackageItems WHERE PackageItemID=1
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
What you say is true but it's an oversimplification of the problem.  If I change the price of "Install tub" I'd have to loop through all packages and update the prices for that item.  I don't want to have to update 10 or 1000 different package prices because I changed the price for the item.  Used to do that, it was messy to maintain.  There might be a better way to store the data and I'm certainly open to it.  I just don't want to have to update 1000 records because I changed one base item.
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
Use this with your existing tables/data:
SELECT TableA.Item, TableA.Price + NZ((SELECT SUM(TableA1.Price) FROM TableB inner join TableA as TableA1 on TableA1.ID = TableB.ItemID Where TableB.BaseItemID = TableA.ID), 0) AS TotalPrice
FROM TableA
WHERE TableA.ID = 1;

Open in new window

I added the NZ() for products that don't have add-on's, otherwise price + NULL = NULL. Only tested in an old Access version by the way.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>If I change the price of "Install tub" I'd have to loop through all packages and update the prices for that item.
then normalize further

ideally you arrive at a design where the price is maintained in one location
PackageItemID  PackageID  ItemID
1               1          901
2               1          1002
3               1          1734

ItemId ItemDesc    ItemPrice
901     Bath Tub     150
1002    Install Tub  100
1734    Faucet (tap) 50

Open in new window

0
 
LVL 84
Comment Utility
If I change the price of "Install tub" I'd have to loop through all packages and update the prices for that item
I agree with PortletPaul - normalize your design further and you'll avoid this.

However - it would seem that a Job performed now would be priced differently than a Job that was performed 5 years ago (or 5 years in the future). So changing the Price by looping through and updating would destroy your historical data - but again, we don't really know what you're doing with the data, so we can't really say.

From my view, it would seem that you'd have a structure much like PortletPaul suggests, and you'd then store a completed (sold? bid?) "package" in another table, where you'd reference the ItemID, ItemDesc, and you'd then use the  ItemPrice as a "default" value, which could be changed to suit the needs of that specific "package".

So in essence, tPackage and tPackageItems would store the "default" (or the framework) of your package, but you'd build a package in another table, and based the defaults on the values pulled from tPackage and tPackageItems. Sort of like an invoicing system, where you would use an Inventory table to prefill defaults for items you'd sell on the Invoice. You'd select an "item", which would fill in values from it's source table (i.e. the ItemDesc and ItemPrice). You could then simply accept those values, or you could overwrite them if you wanted to change the price, or add to the description.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
utterly agree regarding history, some treatment of this is probably needed (or at the least desirable). good point LSM (is it OK if I truncate?)
cheers, Paul

btw: NO points pl - just "chipping in".
{+edit an ooops corrected - sorry}
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
The database presented is just a list of the items we sell.  As you mention a different table stores what we have sold.  The pricing is transferred to the sold table to retain the history.  All I'm trying to do is add several of those items together to make a sales price for an item so the sales people can sell a bathtub installed with all accessories instead of having to remember each item by itself.  Kind of like buying a car instead of 4 wheels, engine etc...
It works fine in my program it's just compiling a report takes forever.  Trying to simplify the report so it doesn't take so long to print.  I really believe my tables are normalized about as far as I can go.  Here's an example of the real data:

SELECT Items.txtItemNumber, Items.txtItemDescription, (Items.curListPrice * Items.curMultiplier) * Items.lngMarkup as Price, PriceBuilder.lngBaseItemID, PriceBuilder.lngItemID, PriceBuilder.lngQuantity, Items.curListPrice, Items.curMultiplier, Items.lngMarkup
FROM Items INNER JOIN PriceBuilder ON Items.txtItemNumber = PriceBuilder.lngBaseItemID
WHERE (((Items.txtItemNumber)=3013));

If you look at the attached image you'll see the result data.  There are 4 records in the pricebuilder table for item 3013.  What I need to do is take the field PricebuilderlngItemID join it to the Items.txtItemNumber then perform the math which would be Sum((Items.curListPrice*Items.curMultiplier)*Items.lngMarkup)*PriceBuilder.lngQuantity.  Then add that total to the calculate Price field for the base item.
Hope this helps a bit.
Data.png
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
Did you try my query? For me it gave the result you wanted (300).
0
 
LVL 84
Comment Utility
I really believe my tables are normalized about as far as I can go
That's hard to say, given the limited details we have about your application, but in general when you run into walls like this (especially regarding "grouped" data), there is almost always something about the table structure. It would help quite a bit if you'd upload a cop of your database, with the sensitive information obfuscated.

In cases where you're dealing with non-normalized data you sometimes have to "build up" queries, which basically means creating stored queries that combine your data, and then using those queries to fetch your data based on your criteria. For example, I might have a query named "qSumByItemNumber" that reads something like this:

SELECT BaseItemID, SUM(MyPriceField) AS TotalPrice FROM MyTable GROUP BY BaseItemID

I could then use that query to fetch the TotalPrice based on my BaseItemID:

SELECT * FROM qSumByITemNumber WHERE BaseItemID=1234
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
In general I see that's a common way to do it, save an extra query and use that, but why do you need it here? In this case the query is used to retrieve the information of 1 article (not the whole list which could lead to performance issues) or do I misunderstand?

Here's an alternative syntax to do it in 1 go:
SELECT A1.ID, A1.Item, A1.Price + IIF(EXISTS (SELECT * FROM TableB AS B WHERE B.BaseItemID = A1.ID), (SELECT SUM(A2.Price) FROM TableB AS B INNER JOIN TableA AS A2 ON A2.ID = B.ItemID WHERE B.BaseItemID = A1.ID), 0) AS TotalPrice
FROM TableA AS A1
WHERE A1.ID = 1

Open in new window

0
 
LVL 2

Author Comment

by:Moed
Comment Utility
Robert,
Could you input my table names for the fictional ones.  Having a little trouble doing that.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
This is entirely derived from the previous, NO points pl.
or? (to avoid one subquery)
SELECT
      A1.ID
    , A1.Item
    , A1.Price + NZ(
                    SELECT SUM(A2.Price)
                    FROM TableB AS B
                    INNER JOIN TableA AS A2 ON A2.ID = B.ItemID
                    WHERE B.BaseItemID = A1.ID
                    ), 0) AS TotalPrice
FROM TableA AS A1
WHERE A1.ID = 1

Open in new window

sorry, just butting in again.
nb: I think NZ works here
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
Uhm, you posted Tables "A" and "B". Is this what you need? :
SELECT A.ID, A.Item, A.Price + IIF(EXISTS (SELECT * FROM B WHERE B.BaseItemID = A.ID), (SELECT SUM(A2.Price) FROM B INNER JOIN A AS A2 ON A2.ID = B.ItemID WHERE B.BaseItemID = A.ID), 0) AS TotalPrice
FROM A
WHERE A.ID = 1

Open in new window

0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
o sorry I see another post now, is it Items & PriceBuilder?
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
Should be something like this. But I'm afraid I can't test now.
SELECT Items.ID, Items.Item, Items.Price + IIF(EXISTS (SELECT * FROM PriceBuilder WHERE PriceBuilder.BaseItemID = Items.ID), (SELECT SUM(Items2.Price) FROM PriceBuilder INNER JOIN Items AS Items2 ON Items2.ID = PriceBuilder.ItemID WHERE PriceBuilder.BaseItemID = Items.ID), 0) AS TotalPrice
FROM Items
WHERE Items.ID = 1

Open in new window

0
 
LVL 2

Author Comment

by:Moed
Comment Utility
Be glad to upload the data.  Here's a .mdb with the two tables.  If you scroll to the bottom of the pricebuilder table you'll see that I quit using several of the columns because I didn't want redundant data.
test.mdb
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
I just saw the field names...:
SELECT Items.txtItemNumber, Items.txtItemDescription, (Items.curListPrice * Items.curMultiplier) * Items.lngMarkup + IIF(EXISTS (SELECT * FROM PriceBuilder WHERE PriceBuilder.lngBaseItemID = Items.txtItemNumber), (SELECT SUM((Items2.curListPrice * Items2.curMultiplier) * Items2.lngMarkup) FROM PriceBuilder INNER JOIN Items AS Items2 ON Items2.txtItemNumber = PriceBuilder.txtItemNumber WHERE PriceBuilder.lngBaseItemID = Items.txtItemNumber), 0) AS TotalPrice
FROM Items
WHERE Items.txtItemNumber = 1

Open in new window

0
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
Comment Utility
Yeah, that does make it harder. I'm not sure about the multiplication yet but this yields results that look ok:
SELECT Items.txtItemNumber, Items.txtItemDescription, (Items.curListPrice * Items.curMultiplier) * Items.lngMarkup + IIF(EXISTS (SELECT * FROM PriceBuilder WHERE PriceBuilder.lngBaseItemID = Items.txtItemNumber),(SELECT SUM((Items2.curListPrice * Items2.curMultiplier) * Items2.lngMarkup) FROM PriceBuilder INNER JOIN Items AS Items2 ON Items2.txtItemNumber = PriceBuilder.lngItemID WHERE PriceBuilder.lngBaseItemID = Items.txtItemNumber), 0) AS TotalPrice
FROM Items
WHERE Items.txtItemNumber=1955

Open in new window

EDIT: some further checking seems to confirm to me it's ok. Only the exact calculation should be double checked.
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
Robert,

Based on my initial calculations it appears to work perfectly.  Let me plug it into the report and be sure and I'll get back to you shortly.  Maybe you could give me a little explanation of your sql statement.   I see what you did but don't really understand the why.

Thanks
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
sure. I wasn't sure why but I saw a problem with NZ() which possibly only works in Access directly but not via Jet. (?!) So I changed it to use IIF(condition, expression1, expression2). This tests condition and if that evaluates to true, then expression1 is returned, otherwise expression2. Here the condition is to check for related records in PriceBuilder, otherwise the SUM would return NULL which you don't want. So if there are no related records the value 0 is used, in other words the product price is not changed.

The tricky part may be the use of Items2, which is the second level of Items: we're selecting from Items for the first level, but to sum the prices of the related products we need to join PriceBuilder with another version of Items so we use the alias Items2 which joins on PriceBuilder.lngItemID as opposed to the original Item which is selected by: WHERE PriceBuilder.lngBaseItemID = Items.txtItemNumber.
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
Haven't had time to test yet but why do you need a second copy of items to join to?
0
 
LVL 2

Author Closing Comment

by:Moed
Comment Utility
Thanks so much for the help.  My report generation time went from 10 minutes to a couple of seconds.  Really, really appreciate the help.
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
My report generation time went from 10 minutes to a couple of seconds
Wow, posting here has been worthwhile then! ;-)

why do you need a second copy of items to join to
to get the price of the linked products. So the main query uses Items to get the product you need, the subquery uses PriceBuilder to get the linked products but needs to link back to Items (with alias Items2) on the linked item id to get the prices of those products. There has been a suggestion by PortletPaul that may not need that step but I haven't tried converting it to your schema.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now