Extract contents of table into one list

[MikeWaring]

We are converting our Product Order system database to be able to send daily order details to the manfacturer's EDI system but we have a problem incorporating certain product codes into this order.

Background:-

We sell products either as a single entity or in a kit that comprises that and other products. Eg, we sell a Car Cleaning Kit that comprises a sponge, manufacuturer's #12345, a wheel cleaning brush, #98765 and a leather, #99889. We give this kit our own product code (MUK80 as an example) which is what the customer orders when he buys.

All those component parts are also sold as single items, using the manufacturer's product codes as quoted above.

We reorder stock daily based on a query that extracts the product codes and quantities from the daily sales invoices. Amongst these codes are of course the "MUK.." kits, but as the manufacturer doesn't recognise these codes we can't include these on the EDI order.

We have the details of these kits contained in a table; col 1 is the kit product code (eg MUK80) and the other columns separately contain the consist part codes and quantity of each consist. To use the example above, MUK80 has #12345 qty 1, #98765 qty 1, #99889 qty 1.

A kit can have anything up to 6 consists. So the table consists of 13 Cols - the Kit product code and 6 columns for consist product codes and quantities. At this time we have 130 different kits and increasing.

What I need to do is to be able to extract the consist #'s and qty of the kits sold and add these to the order list. This list may already have some of the parts on there as they could also have been sold as a single item.

To give an example of this, let's say we sell 3 of the MUK80 Car cleaning kits; At the moment the MUK80 qty 3 will appear on the reorder query, but what I need is to get the consist parts of that kit added to the list instead.

So as a final example, let's say we also sold 2 sponges #12345 and one brush #98765 as products in their own right; the EDI order for that day should therefore have #12345 total qty 5 and # 98765 total qty 4.

The trick I'm missing is how to get those consist parts onto the final reorder output which is a csv file containing only 2 columns, Prodcut Code and Total Quantity.
We have a macro that exports this order as a csv file that we then convert to xml outside of access.

I would really appreciate any help on this - a query would be my preferred option ( I'm none too bright using VBA) but I'm up for the challenge should it be necessary.

Sorry for the longwinded post, but I thought a detailed explanation may help frame the problem.



Thanks in anticipation....
Mike

[Rixxe]

Hi Mike,

This looks interesting....

To give an example of this, let's say we sell 3 of the MUK80 Car cleaning kits; At the moment the MUK80 qty 3 will appear on the reorder query, but what I need is to get the consist parts of that kit added to the list instead.

So as a final example, let's say we also sold 2 sponges #12345 and one brush #98765 as products in their own right; the EDI order for that day should therefore have #12345 total qty 5 and # 98765 total qty 4.

Basically you just want to list all items from any package?
Would the kit MUK80 change at any point? Or is it always the same?

For example if you had another table that lists the properties of all your 'kits', i'm sure you could have a query displaying those properties instead...

 
SELECT b.*
FROM tblkits as a
INNER JOIN tblfullproperties as b
on a.ID=b.ID

Obviously you would use whatever product code that would link the 2 tables, in place of ID.

if you select MUK80, it should display all the properties of that product from the fullproperties table

However, I reckon some VBA is probably needed at some point depending on how you want it displayed.

Good luck

[MikeWaring]

Hi Rixxe, thank you for your rapid response.

What we need to do is (a)to capture the quantity and product codes of individual items that have been sold as part of the kits and
(b) add this to the daily report that contains all other products sold that day so they can be re-ordered. This report could already contain some of those product codes as they could also have been sold as items in their own right; in these instances, I need only the qty adding to the existing product code on the order rather than adding the product code again (in other words no duplication of product codes on the order report).
Would it help if I sent a zip file of a sample db so you can see the structure? It may help you play around and see what could be done.
Kind rgds
Mike

[MikeWaring]

Sorry Rixxe - I didn't answer the question re the kits changing. The answer is no, unless the manufacturer changes any of the product code #s. If so, we would update the MUK kit consist to reflect the change of #. In reality, this doesn't happen a great deal.
Kind regds
Mike

[Rixxe]

What we need to do is (a)to capture the quantity and product codes of individual items that have been sold as part of the kits and
(b) add this to the daily report that contains all other products sold that day so they can be re-ordered. This report could already contain some of those product codes as they could also have been sold as items in their own right; in these instances, I need only the qty adding to the existing product code on the order rather than adding the product code again (in other words no duplication of product codes on the order report).

Right, As far as (a) is concerned:
This is where my query would come into action, it would need changing to make it more in depth, but in essence, where a kit is shown, replace with individual components/items.

(b) Adding to a field isn't a problem. Using the COUNT() function can also give you the quantity of the same product/s.
Obviously this would have to be done before it is added to the report. (I guess)

I'm afraid i don't use VB, and so i cannot help you incorperate that with SQL code, however i use some C# so i still might be able to give you some ideas.
If you want to load the DB so others may also have a look at what you want, this might help you get more input.

Regards

Rixxe.

[MikeWaring]

Rixxe,
I've attached a sample db that should make sense of what I'm trying to do.
the query "Toolstream Product order with prices" forms the basis of the re-order report, but you will see that it has the "MUK" codes in the 1st column. What we need is for the respective consist parts & qtys that appear in the adjacent columns to be in the 1st col in place of the "MUK" number.

I'm not conversant with VBA or SQL - Ive created everything from the design panes or wizards.

[Rixxe]

Hi Mike,

Had a look at you DB, personnally from seeing what you have done: I'd create another query which gives you the properties of MUK, the costs of those properties, and any other information that you have in the report.

Then Union the tables together, and perform counts if needed. I think its the best way to get the job done.
Alter the first query to remove the MUK, UNION the second table to the first. Do any more calcuations (i.e. adding/totals/qty) and hopefully then you can put it straight into a report.

There might be another way to do it, which invloves just your original query, maybe someone else might have an idea.
I'll see what else i can come up with when i have some time.

So from the second table:

 
Select a.PropertiesOfMuk
from MUKpartconsists as a
inner join MUK as b
on a.MUKID=b.MUKID
 
UNION (if you want it to be distinct) or UNION ALL (if you dont)
 
Toolstream product order with prices Tbl (Its actually named this query, but might be best to have it as a table)

Note: When using UNION, you need to have the fields the same if i remember rightly, or search for other ways to combine the tables.

This should give you all the parts of MUK, without MUK. Do any calcuations if needed, the continue and put it into the report once again.

Good luck

(Hope i made some sense)

Rixxe.

[MikeWaring]

Hi Rixxe, thanks for sticking with this...
What you say makes perfect sense, its just that I'm not familiar with how to use the code you have written.. Also, I haven't used the "Union" function as yet. I'm a little like a piano player that can't read music - I can tinker with the db but am not that good at transferring theory into practice (if you know what I mean).
Thanks again; I look forward to any more help you can give.
Kind rgds
Mike

[Rixxe]

I understand what you mean, i was in the same position before! (With an actual piano)

http://www.fabalou.com/Access/Queries/unionquery.asp

That site should be helpful to you, as far as how union queries look:

select supplier_id
From suppliers
UNION
select supplier_id
from orders;

This is a basic version, you select the fields which are relevent in both tables that you have, you use either UNION (as above) if you want it to be distinct, or UNION ALL if you don't.

I'm afraid i don't use the wizards much (Dont really work in access much) so i wouldn't be able to tell you how to do it. Either attempt a query as above, or i'm sure there is a site out there to help you do it via the wizard.

Let me know how it goes, or if you want me to use your table names, ill create the query for you.

Good luck

[MikeWaring]

Hi Rixxi, I've had a stab at this but no joy - no doubt down to my lack of knowledge. Would you have a go at creating the query for me as you suggested in your last post?
I have some other things that I may need further help with, but I'll do another post if I get stuck.
Thanks again.
Best rgds
Mike

[Rixxe]

Hi Mike,

Had to have a read to catch up (poor memory at best)..
So you have a query called [Toolstream Product order with prices], if you redo the query to not include all the MUK's.
So add this into the where clause:
WHERE [Reorder Products Code] <> 'MUK*'

That should give you [Toolstream Product order with prices] (Without MUK) and make it a table. [Toolstream Product order with prices Table]

Create a query, with all the parts of MUK (Consist1,2,3 etc) into a single column and call that [Reorder Product Code], Get the quantities in another column and name that [SumOfQuantity]. Turn that into a table and call it [Toolstream Product Order without MUK] or something....

Then:

 
SELECT a.[Reorder Product Code], a.SumOfQuantity
INTO [Toolstream Product Combined]
FROM [Toolstream Product order with prices Table] as a
UNION ALL
SELECT a.[Reorder Product Code], a.SumOfQuantity
FROM [Toolstream Product Order without MUK] as a
ORDER BY [Reorder Product Code]

You can change the tables names as you wish.

So after all that, you should have a table called Toolstream Product Combined, with all the components of MUK and the original list that you had.

Perform the same query to get the prices etc, and you can shove it into a report.
FYI There are other ways of doing this.

Good luck

[MikeWaring]

Hi Rixxi, thanks for the reply.
I'm proficient enough to do the queries, tables etc, but at the risk of sounding a numpty, what do I do with the code? Where do I enter it? Do I have to create a VBA module (or whatever its called in Access)? This area of Access is one that I've never touched, so I'm completely in the dark with it.
Sorry to keep coming back to you, but this part of the database is really important for us to be able to order the correct amount of stock daily.
Rgds
Mike

[Rixxe]

Hi Mike,

Sorry for the late reply:
If you open up a new query, and select the SQL view, you can then put SQL code (As in post 11) in there.
Click the run button as you would normally, and it should work.
Obviously you will have to do as i explained in that post or you wont have the table/field names to run the Union query.
It would be (Depending if you plan to do more on this subject) a good idea to learn VBA and SQL code, there are plenty of sites around that have free tutorials.

Good luck

[MikeWaring]

Hi Mike,

Had to have a read to catch up (poor memory at best)..
So you have a query called [Toolstream Product order with prices], if you redo the query to not include all the MUK's.
So add this into the where clause:
WHERE [Reorder Products Code] <> 'MUK*'

That should give you [Toolstream Product order with prices] (Without MUK) and make it a table. [Toolstream Product order with prices Table]

Create a query, with all the parts of MUK (Consist1,2,3 etc) into a single column and call that [Reorder Product Code], Get the quantities in another column and name that [SumOfQuantity]. Turn that into a table and call it [Toolstream Product Order without MUK] or something....

Then:

 
SELECT a.[Reorder Product Code], a.SumOfQuantity
INTO [Toolstream Product Combined]
FROM [Toolstream Product order with prices Table] as a
UNION ALL
SELECT a.[Reorder Product Code], a.SumOfQuantity
FROM [Toolstream Product Order without MUK] as a
ORDER BY [Reorder Product Code]

You can change the tables names as you wish.

So after all that, you should have a table called Toolstream Product Combined, with all the components of MUK and the original list that you had.

Perform the same query to get the prices etc, and you can shove it into a report.
FYI There are other ways of doing this.

Good luck

Hi Rixxie I'm still struggling with this - the bit I'm coming unstuck on is

Create a query, with all the parts of MUK (Consist1,2,3 etc) into a single column and call that [Reorder Product Code], Get the quantities in another column and name that [SumOfQuantity].

This has been my issue all along - how to get the data from several columns into one column! I've tried doing an append query but no joy, so now I've run out of talent, or am I just staring at an obvious method and not seeing it?

Your furher advice would be much appreciated!
Kind regards
Mike

[Rixxe]

Mike,

SELECT a.Consists1
INTO TblConsists1
FROM PartsOfMUK as a

Append Tables (i.e append query, dont mistake this for code ) to TblConsists1

SELECT a.Consists2
INTO TblConsists2
FROM PartsOfMUK as a

Append Tables to TblConsists1

SELECT a.Consists3
INTO TblConsists3
FROM PartsOfMUK as a

If you want to do it only for a certain MUK (Say MUK1000) add this in under the from clauses:
WHERE MUKFIELD (whatever the field is with MUK in it) = 'MUK1000'