Confused trying to produce results in a query

[totalstripe]

This is several issues all (I think) with the same or similar problem.

Basically our products contain multiple items from our supplier. For example we may supply a pencil & rubber gift set which is made up of a pencil a rubber & gift packaging provided by our supplier seperately. We would like to create an inventory that understands that when someone purchases the gift set, we loose a pencil, a rubber & the gift packaging. I can make access work the sale of a pencil, rubber & gift packaging separately but not all included.


The crux of this (I think) is that at the moment I can't get access to read what is in one field and make the next field draw a related result from a different table. For example:

I am trying to create a query that basically has our item code, the supplier code and then the price of the item from our supplier.

However I have the supplier code & price linked on another table & want it to automatically bring up the price when you type in the supplier code into the adjacent field.

Any Ideas? (and I appreciate I probably haven't explained this very well!)

 

[Bilbo_Baggins_Esq]

well, you list two different problems.

first, for your kits, you'll need to create a table with a kit name and or ID and then the product ID of each component for the kits will each get a record associated with that kit ID.
Then you can work a query that will pull all the components required for a given kit, and also be able to then use the resultant product IDs and calculate how many kits you can produce based on existing, or ordered inventory.

Secondly, when you're building a query, it is required to have all of the tables and/or queries which contain your desired fields, contained in the actual query.

 

[DCrake]

You are actually looking at what is called "Bill of Materials" You product code for the Gift set is GS123 and GS123 has components, therefore in you product components table you have components Pencil GS123-1, Rubber GS123-2 and GiftWrap GS123-3

Now you may say Ok but we use the same pencil for all our gift sets do I need to create a component record for each gift set pencil, no, just change the code in you component table to GS-1. So now you want to assemble a gift set and reduce the stock levels accordingly. So by removing 1 gift set from stock you can code it to go to the component table and reduce by one the stock levels for all items that are prefixed GS123 or GS.

Now if you have products that do not have components then so what still have it in the components table as a single item. You components table can have 1 to 1k items as long as they are linked to a product or products the stock level will reduce by 1 for the number of products chosen.

David

Clear as mud.

 

[totalstripe]

I think the first of these answers seems more relevant.

This is what I currently have, how can I adapt this to work?

(P) = Primary Key

Stock Table:
-Suppliers Item Code (P)
-Supplier
-Description
-Cost
-VAT Rate

Stock Extended Query (basically the same as the stock table but with a calculated column to work out the cost of each item inc VAT):
-Stock*
-Cost inc VAT

Products Table:
-Our Item Code (P)
-Description
-Our Sale Price
-Item 1 (suppliers item code is entered here for each item)
-Item 2 (suppliers item code is entered here for each item)
-Item 3 (suppliers item code is entered here for each item)

I would first like to create a query that had the following fields:

-Our Item Code
-Our Item Description
-Our Sale Price
-Item 1
-Item 1 Price
-Item 2
-Item 2 Price
-Item 3
-Item 3 Price
-Total Price (I can manage this calculation)

I just don't know how to create the query to bring up the item prices. Am I trying something silly or just missing the blantantly obvious?

 

[Bilbo_Baggins_Esq]

You need to have a means of relating the suppliers item code to your item code for individual items.

And/Or, you need to decide on a method of keeping a BOM (build of materials) as described above twice.

 

[totalstripe]

Does my Products table which has My Item Code & the suppliers Item code in a list not do this? How do I ensure access creates this relationship?

(for reference the codes in the Products table for each item are in a lookup field directed from the Stock Table)

 

[Bilbo_Baggins_Esq]

Well, i see some potential problems with this design. Among them is that you will be limited to only kits which include 3 parts. Using the design you specified, the below SQL will allow you to have kits which may have less than three parts, but still you will be limited to only three parts per kit.

SELECT [Products Table].[Our Item Code], [Products Table].Description, [Products Table].[Our Sale Price], [Products Table].[Item 1], [Stock Extended Query].[Cost inc VAT], [Products Table].[Item 2], [Stock Extended Query_1].[Cost inc VAT], [Products Table].[Item 3], [Stock Extended Query_2].[Cost inc VAT]
FROM (([Products Table] LEFT JOIN [Stock Extended Query] ON [Products Table].[Item 1] = [Stock Extended Query].[Suppliers Item Code]) LEFT JOIN [Stock Extended Query] AS [Stock Extended Query_1] ON [Products Table].[Item 2] = [Stock Extended Query_1].[Suppliers Item Code]) LEFT JOIN [Stock Extended Query] AS [Stock Extended Query_2] ON [Products Table].[Item 3] = [Stock Extended Query_2].[Suppliers Item Code];

On the other hand, I might suggest this among possible alternative designs whioch would allow unlimited number of parts per kit:
Stock Table:
-Suppliers Item Code (P)
-Supplier
-Description
-Cost
-VAT Rate

Products Table:
-Our Item Code (P)
-Our Description
-Our Sale Price

Kit Table:
-Our Item Code
-Suppliers Item Code


Then this SQL would show you each of your Item Codes, the number of pieces in each Kit, the total cost (incl VAT) of each kit, your price, and profit.

SELECT [Products Table].[Our Item Code], Count([Kit Table].[Our Item Code]) AS CountOfParts, Sum([Stock Table]![Cost]+([Stock Table]![Cost]*[Stock Table]![VAT Rate])) AS [Our Cost], Avg([Products Table].[Our Sale Price]) AS [Our Sale Price], [Our Sale Price]-[Our Cost] AS Profit
FROM ([Kit Table] INNER JOIN [Products Table] ON [Kit Table].[Our Item Code] = [Products Table].[Our Item Code]) INNER JOIN [Stock Table] ON [Kit Table].[Suppliers Item Code] = [Stock Table].[Suppliers Item Code]
GROUP BY [Products Table].[Our Item Code];

 

[gemma-the-husky]

you should be aware that inventory/stock is extremely difficult anyway

adding kits/part explosions makes the design even harder

you need to design the system so that the kit is shown as a pickable item, but is a "phantom" for the exploded parts

then you need to decide what you do if not all the items in the kit are available

do you ship what you have, and raise backorders etc ...or do you cancel the whole order


probably loads of other critical areas also

 

[totalstripe]

Well, i see some potential problems with this design. Among them is that you will be limited to only kits which include 3 parts. Using the design you specified, the below SQL will allow you to have kits which may have less than three parts, but still you will be limited to only three parts per kit.

SELECT [Products Table].[Our Item Code], [Products Table].Description, [Products Table].[Our Sale Price], [Products Table].[Item 1], [Stock Extended Query].[Cost inc VAT], [Products Table].[Item 2], [Stock Extended Query_1].[Cost inc VAT], [Products Table].[Item 3], [Stock Extended Query_2].[Cost inc VAT]
FROM (([Products Table] LEFT JOIN [Stock Extended Query] ON [Products Table].[Item 1] = [Stock Extended Query].[Suppliers Item Code]) LEFT JOIN [Stock Extended Query] AS [Stock Extended Query_1] ON [Products Table].[Item 2] = [Stock Extended Query_1].[Suppliers Item Code]) LEFT JOIN [Stock Extended Query] AS [Stock Extended Query_2] ON [Products Table].[Item 3] = [Stock Extended Query_2].[Suppliers Item Code];

On the other hand, I might suggest this among possible alternative designs whioch would allow unlimited number of parts per kit:
Stock Table:
-Suppliers Item Code (P)
-Supplier
-Description
-Cost
-VAT Rate

Products Table:
-Our Item Code (P)
-Our Description
-Our Sale Price

Kit Table:
-Our Item Code
-Suppliers Item Code


Then this SQL would show you each of your Item Codes, the number of pieces in each Kit, the total cost (incl VAT) of each kit, your price, and profit.

SELECT [Products Table].[Our Item Code], Count([Kit Table].[Our Item Code]) AS CountOfParts, Sum([Stock Table]![Cost]+([Stock Table]![Cost]*[Stock Table]![VAT Rate])) AS [Our Cost], Avg([Products Table].[Our Sale Price]) AS [Our Sale Price], [Our Sale Price]-[Our Cost] AS Profit
FROM ([Kit Table] INNER JOIN [Products Table] ON [Kit Table].[Our Item Code] = [Products Table].[Our Item Code]) INNER JOIN [Stock Table] ON [Kit Table].[Suppliers Item Code] = [Stock Table].[Suppliers Item Code]
GROUP BY [Products Table].[Our Item Code];

I tried the second of these suggestions, however the code you gave doesn't appear to be correct, when I enter it I get returned

"The syntax of the subquery in this expression is incorrect.
Check the subquery's syntax and enclose the subquery in parentheses."



Also the code [Our Cost] bears no relation to any table or field in my database what is this meant to represent?

 

[Bilbo_Baggins_Esq]

Hmmm, well you can't run the second query in your db without changing your design.
Did you actually add the "Kit Table" and change your products table?

As for the syntax, I'm working in Access 07, but I am not aware of any SQL syntax changes in Access 07 that would cause a syntax issue.

Also, I don't think the query will actually run until you save it because the query actually uses some of its own fields for calculation in the Profit field. Other than that, there is no sub query involved.

As for the "Our Cost", it is the name of the field in the query. I gave it a different name because it actually represents the Kit's total cost, and not any single part's individual cost.

 

[totalstripe]

I now have three tables as described below:

Products:
Item Code (P)
Description
Price

Stock:
Suppliers Code (P)
Supplier
Description
Cost per Unit
VAT Rate

Kit:
ID (P)
Item Code
Suppliers Code

I have replaced relevant field & table names in the code but this doesn't help.

I am also running access 2007 and it won't let me save the query until the error is resolved.

What fields should I have in the query?

 

[Bilbo_Baggins_Esq]

oops, sorry, I get it.

Try this SQL instead as the first step

SELECT [Products Table].[Our Item Code], Count([Kit Table].[Our Item Code]) AS CountOfParts, Sum([Stock Table]![Cost]+([Stock Table]![Cost]*[Stock Table]![VAT Rate])) AS [Our Cost], Avg([Products Table].[Our Sale Price]) AS [Our Sale Price]
FROM ([Kit Table] INNER JOIN [Products Table] ON [Kit Table].[Our Item Code] = [Products Table].[Our Item Code]) INNER JOIN [Stock Table] ON [Kit Table].[Suppliers Item Code] = [Stock Table].[Suppliers Item Code]
GROUP BY [Products Table].[Our Item Code];

Then reopen the query in SQL and paste the original SQL back in and it should let you save it then.

 

[Bilbo_Baggins_Esq]

errr, I see you changed some names.

Here is an updated stage 1

SELECT [Products].[Item Code], Count([Kit].[Item Code]) AS CountOfParts, Sum([Stock]![Cost per unit]+([Stock]![Cost per unit]*[Stock]![VAT Rate])) AS [Our Cost], Avg([Products].[Price]) AS [Our Sale Price]
FROM (Kit INNER JOIN Products ON [Kit].[Item Code]=[Products].[Item Code]) INNER JOIN Stock ON [Kit].[Suppliers Code]=[Stock].[Suppliers Code]
GROUP BY [Products].[Item Code];

save it
reopen it
then paste this in

SELECT Products.[Item Code], Count(Kit.[Item Code]) AS CountOfParts, Sum([Stock]![Cost per unit]+([Stock]![Cost per unit]*[Stock]![VAT Rate])) AS [Our Cost], Avg(Products.Price) AS [Our Sale Price], [Our Sale Price]-[Our Cost] AS Profit
FROM (Kit INNER JOIN Products ON Kit.[Item Code] = Products.[Item Code]) INNER JOIN Stock ON Kit.[Suppliers Code] = Stock.[Suppliers Code]
GROUP BY Products.[Item Code];

then save it
this will add the profit column back in

 

[totalstripe]

Thank You Very Much this works great!!!

Now to the other half of my initial problem.

How do I create an Inventory for this same stock. I currently have the following tables.

Orders:
Order Number (P)
Customer Number
Order Date
Total Product Price
Postage Method
Postage Cost
Total Price
Payment Method
Payment Reference

Orders Detail:
Order Number (P)
Item Code (P)
Quantity
Price per Unit
Additional Details

Kit:
ID (P)
Item Code
Suppliers Code

Purchase Orders:
Order Number (P)
Supplier
Order Date
Total Products Price
Shipping Cost
Total Price
Payment Method

Purchase Orders Detail:
Order Number (P)
Suppliers Code (P)
Quantity
Price per Unit

Stock:
Suppliers Code
Supplier
Description
Cost per Unit
VAT Rate

I think this is all the relevant information. Do I need any more tables initially, following that what do I need in the query to produce an inventory list showing:

Suppliers Code
Description
Item Cost inc VAT
Quantity Held
Quantity on Back Order
Reorder Amount
Target Level
Quantity Required
Cost inc VAT of Held Stock
Cost inc VAT of Back Order Stock
Cost inc VAT of Stock Required

If you would like me to create this as a seperate thread I can do just let me know and I will open it up.

Thanks Again for your help so far!

 

[Bilbo_Baggins_Esq]

That's just a basic joined query.

Of course you need to define how you want to calculate "Reorder Amount", "Target Level", "Quantity Required"

 

[totalstripe]

So if I was to add data into say the stock table regarding the reorder amounts, target levels, qty required then I should be able to just produce a query that will work as an inventory?

I apologise my access knowledge is limited so learning as I go along, I probably know this already but what do you mean by joined query?

 

[Bilbo_Baggins_Esq]

yep!

enjoy!