Hello, I have a question regardi

[ah6]

Hello,

I have a question regarding the creation of an SQL query,
but I'll need to give some background first.

I build and sell products that are made up of a variety of
parts.

Usually, an order will come in consisting of 2 or 3
products, (which in turn consists of multiple parts), and
any particular part may be part of more than one final product, i.e a many-to-many relationship.

As each item is stored in a different location, it is an
inefficient use of my time to get all the parts for the 1st
product, then all the parts for the 2nd product etc,
especially when they have parts in common.

I'm trying to create a web based (ASP) application that
will allow me to enter the quantity of each product required, but then produce a list by Part Number, so I
retrieve all common parts together.

I have an Access database with the following tables and
example data:

tbl_Parts
=========
PartNumber (Primary Key) | PartName | StorageLocation |etc.
-------------------------|----------|-----------------|----
5631 | Test 1 | Test A |
5675 | Test 2 | Test B |
5805 | Test 3 | Test C |


tbl_UpgradeKits
===============
UpgradeKitNo (PK) | Name | etc.
------------------|------|-----
UG1 | T1 |
UG2 | T2 |


tbl_UG_Parts (resolving the many-to-many)
============
UpgradeKitNo (PK) | PartNumber (joint PK) |
------------------|-----------------------|
UG1 | 5631 |
UG1 | 5675 |
UG1 | 5805 |
UG2 | 5675 |
UG2 | 5805 |

This database was created by myself purely for the use of
the ASP website, we already have another system in place
that takes care of the ordering and invoicing. Therefore I
don't need to /store/ quantity ordered etc.

So finally I get to my question (sorry about the wait!):
If I have an ASP page that reads in the quantity of the
Upgrade Kits required e.g 1 x UG1 and 2 x UG2, what SQL
queries should I use to find out what parts a product
consists of, AND THEN produce a list saying I need (in this
example):
1 x 5631
3 x 5675
3 x 5805

Thanks in advance for your help, and sorry about the long
post, but I wanted to provide as much information as possible.

 

[krisbrixon]

One problem with your database:

It is conceivable that a single upgrade kit may have two of the same part to be used in the upgrade, if so then your primary key will fail.

Ignoring that problem for now.

I would make a sql statement that returns all the parts for each kit and then create a looping thing to add the quantity to the form.

(NOTE: The code below is just to show what I was thinking, it is missing some parts and will not work in the way shown.)

------------------------------------

Dim aKits(,)
'Stick all the selected upgrade kits and how many in the array
Dim aParts(,)
'Latter stick in all the part numbers and the quantity

sSQL = "SELECT ugp.PartNumber,p.PartName,p.StorageLocation,ugp.UpgradeKitNo,u.Name "
sSQL = sSQL & "FROM tbl_Parts AS p, tbl_UpgradeKits AS u, tbl_UG_Parts AS ugp "
sSQL = sSQL & "WHERE ugp.UpgradeKitNo = u.UpgradeKitNo "
sSQL = sSQL & "AND ugp.PartNumber = p.PartNumber "
sSQL = sSQL & "AND ugp.UpgradeKitNo IN ("
For i = 0 to UBound(aKits)
sSQL = sSQL & "'" & aKits(0,i) & "',"
Next
sSQL = Mid(sSQL,1,Len(sSQL)-1) 'remove last comma
sSQL = sSQL & &quot ORDER BY ugp.PartNumber"

'then loop the array with each record to get the quantity.

While not objRS.eof
For i = 0 to ..akits..
If aKits(0,i) = objRS("UpgradeKitNo&quot Then
bFound = FALSE
For j = 0 to ..aparts..
If aparts(0,j) = objRS("PartNumber&quot Then
aparts(1,j) = aparts(1,j) + 1
bFound = TRUE
End If
Next
If bFound = FALSE Then
'Add this part to aparts
redim preserve aparts(1,ubound...)
aparts(0,ubound...) = objRS("PartNumber&quot
aparts(1,ubound...) = "1"
End If
End If
Next
Wend

'Finally loop through 'aparts' for your results.

------------------------------------

Kris
- To err is human, but to really foul things up requires a computer.

 

[ah6]

Thanks for that I'll give it a try.

Regarding the primary key, any particular kit may consist of many parts, but only 1 of each. But yes, in other situations the key would fail, so thanks for pointing that out.