This is what I have needed to do so far:
1. Select all the rows in table TransactionEntry that have column SalesRepID = 0
2. These rows have two columns I need to work with, the first is ItemID and the second is Quantity - I need to get the total Quantity for all items with the same ID and I need to then use the ItemID value to select all the rows in the table Item that have ID equal to the ItemID in TransactionEntry
3. From these rows in Item I need to get the value of their column ItemLookupCode - I then need to pair the ItemLookupCode from Item with the total quantity for that ItemID in TransactionEntry
Now, to do that I have spent hours and hours creating this query:
Code:
SELECT Item.ItemLookupCode, Sum(TransactionEntry.Quantity) AS QtySum
FROM Item INNER JOIN TransactionEntry ON Item.ID = TransactionEntry.ItemID
GROUP BY Item.ItemLookupCode, TransactionEntry.SalesRepID HAVING TransactionEntry.SalesRepID = 0
This works quite well and I am able to do that I need with it. Now though I would like to be able to generate purchase orders from this information. These are stored in another table. (By the way, this is work for the church / youth club where I grew up, they run a shop to try and fund the activities they do because it's a pretty poor neighbourhood. I'm trying to do this for them to remove as much of the technical stuff as possible for them.)
To generate a purchase order I need to do the following:
4. Insert a new row into table PurchaseOrder with ID and PONumber that are not necesarily equal but are both 1 higher than their previous values. I think ID can auto increment but I don't think PONumber can. I also need to set Status equal to 0, a column named DateCreated should have a value of DD/MM/YYYY
5. Insert a new row into table PurchaseOrderEntry
for each Item from step 2 above with ID that auto increments, PurchaseOrderID that is the same as the ID in PurchaseOrder table and ItemID is the same as the ID from step 2 above and QuantityOrdered equal to the total quantity from step 2 above and the price should be equal to the Price field in the Item row as above.
Here's a quick text mockup to hopefully better explain my problem:
Code:
|--------------------------------|
|Table: TransactionEntry |
|--------------------------------|
| SalesRepID | ItemID | Quantity |
|------------|--------|----------|
| 1 | 1 | 3 |<- this row will be ignored.
| 0 | 1 | 4 |<- |these two rows will have their
| 0 | 1 | 54 |<- |quantities added
| 0 | 2 | 43 |
| 0 | 3 | 33 |
|--------------------------------|
|-----------------------------|
|Table: Item |
|-----------------------------|
| ID | ItemLookupCode | Price |
|----|----------------|-------|
| 1 | 10001 | 24.95 |<- this row would be selected from the data above and it's quantity would be 57 (54 + 3) so I would have ItemLookupCode = 10001, Quantity = 57
| 2 | 10043 | 23.45 |<- ItemLookupCode = 10043, Quantity = 43 [as above]
| 3 | 20234 | 19.95 |
|-----------------------------|
The tables above will only be read from, not inserted to. All the tables below will have their contents inserted by me. These are the queries I need to create.
|--------------------------------------|
|Table: PurchaseOrder |
|--------------------------------------|
| ID | PONumber | Status | DateCreated |
|----|----------|--------|-------------|
| 1 | 000001 | 0 | 22/02/2004 |<- this would be a sample row.
| 2 | 000002 | 0 | 23/02/2004 |<- this is the type of row I'd like to insert with ID, PONumber being increased by 1 automatically, status always = 0 and DateCreated = the current DD/MM/YYYY
|--------------------------------------|
|---------------------------------------------------------|
|Table: PurchaseOrderEntry |
|---------------------------------------------------------|
| ID | PurchaseOrderID | ItemID | QuantityOrdered | Price |
|----|-----------------|--------|-----------------|-------|
| 15 | 00001 | 1 | 57 | 24.95 |<- sample row, PurchaseOrderID = PONumber from above, Price is the same price as the row in Item with ID = ItemID and QuantityOrdered = the total quantity from above
| 16 | 00002 | 2 | 43 | 23.45 |<-| both rows have the same PurchaseOrderID because that purchase order contained orders for Items 2 and 3
| 17 | 00002 | 3 | 33 | 19.95 |<-| this is of the parts I'm having most trouble with!
|---------------------------------------------------------|
I might not have explained all of this very well so if you have any questions just ask. I'd really appreciate if anyone could create a query to do this for me or even give me a point in the right direction.
Thanks a lot,
Regards,
Bob
Complex (to me) nested queries spanning 4 tables!
