We are going over to ordering products using the manfacturer's EDI system but we have a problem incorporating certain product codes that we sell.
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 set that comprises a sponge, a wheel cleaning brush and a leather. 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 code.
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 kits, but as the manufacturer doesn't recognise these codes we can't include these on the EDI order.
We have the list of these kits contained in a table; col 1 is the kit product code and the other columns separately contain the consist part codes and quantity of each consist. A kit can have anything up to 6 consists. So the table consists of 13 Cols. At this time we have 130 different kits and increasing.
What I need to do is to be able to extract the consist parts 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; The MUK80 will appear on the reorder query, but what I need is to able to get the consist parts of that kit added to the list, which let's say may already include 2 sponges and one brush that have been sold independently.
If necessary, I can get the existing reorder query to ignore the MUK products and I can write another query to capture these.
The trick I'm missing is how to get those consist parts onto the final reorder. 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
Bookmarks