Create SubID based on Category

[Steve777]

Hello,

I am struggling to find a code to automate the following simplified situation. I am able to understand VBA examples but not really capable to create something that works myself :-(
I have Table1 that will be loaded daily with new data and then combined with Table2 for the ReqCat column and some other data that is related to CaseID

Out of this query1 (Table1 & Table2 combined) I would need some VBA automation in order to append the data in Table3 and create a SubID based on the ReqID and a consecutive number as below.
It should just append data if the record is not already available in Table3 (ReqID, CaseID).
The appended records should be then removed in Table1.


Table1 & Table2 (Query1)
ReqID / ReqCat / Case ID
1 30 A
1 30 B
1 55 C
1 55 D
1 60 E
2 80 B
2 80 C
2 10 A


Table3
ReqID / SubID / ReqCat / Case ID
1 1-1 30 A
1 1-1 30 B
1 1-2 55 C
1 1-2 55 D
1 1-3 60 E
2 2-1 80 B
2 2-1 80 C
2 2-2 10 A


Any help would be highly appreciated.

Thank you.
Steve

 

[Beetle]

First, unless there is some other reason that Table3 needs to exist, I would recommend that you do this in a query rather than redundantly writing this data to another table.

Second, you're going to need some way to order the record set. The example data you posted doesn't appear to have a coherent order. The first 5 records appear to be ordered by ReqID, ReqCat, CaseID but then after that the order fails.

For this example, I am going to assume the above order for all the records, so we start out with a data set like;

ReqID ReqCat CaseID
1       30       A
1       30       B
1       55       C
1       55       D
1       60       E
2       10       A
2       80       B
2       80       C

Then a query with a couple of sub queries like this;

SELECT Query1.ReqID, [ReqID] & "-" & (Select Count(*)
FROM (Select Distinct ReqID, ReqCat From Query1 As T)
WHERE T.ReqID = Query1.ReqID And T.ReqCat < Query1.ReqCat)+1 AS SubID, Query1.ReqCat, Query1.CaseID
FROM Query1;

Returns the following results;

ReqID SubID ReqCat CaseID
1      1-1    30      A
1      1-1    30      B
1      1-2    55      C
1      1-2    55      D
1      1-3    60      E
2      2-1    10      A
2      2-2    80      B
2      2-2    80      C

Not the exact order you had in your original post, but it might give you an idea how to proceed. Like I said, if you want that order you're going to need some field by which to determine that.

 

[Steve777]

Thank you very much Beetle

The data is not sorted and your query is what I need to create one SubID per ReqCat.
I assume I can append your query to a table as I still need Table3 to track a few things separate from Table1 & 2. And I need to make sure that once a SubID is assigned to a record it should not change anymore.

I was thinking where a similar situation exists in terms of database design, so you might be able to suggest in general a better design:

For example in a Customer order database, where customers order several products/materials per order. If a company carries no inventory and buys all products/materials directly from different suppliers the Order would trigger automatically several purchases from suppliers depending if a customer order contains products/materials from different suppliers. Therefore an order would need to be split in several purchases with multiple PurchaseID linked to the OrderID.

So far I have just seen database examples (e.g. Northwind) that separate Customer Orders from Purchases from Suppliers because there is inventory.

But to enter the data twice if there is no inventory makes not really sense. But there are still 2 separate tables required to create the reports (e.g. Invoice, Purchase, ...) to handle the situation.

Any idea?

I am still hoping for my problem to find a better solution.

Cheers
Steve

 

[spikepl]

Your table 3 contains copy of data from 1 & 2. Why? A report does not require separate tables, but separate queries.

I have in fact the siuation you describe: a customer order with order details generates immediately numerous purchase orders. All the order detail info in each purchase order is the same as in the customer order, just sorted by supplier, and listing the supplier item codes rather than the in-house ones. Each item in the tblItems carries itemcode, supplier item code and supplier id.

The only data a purchase order needs stored is the purchase order number, date, and id of the order to which it pertains. All the rest can be queried and a report (PO) generated from that.

 

[Steve777]

Your table 3 contains copy of data from 1 & 2. Why? A report does not require separate tables, but separate queries.

I have in fact the siuation you describe: a customer order with order details generates immediately numerous purchase orders. All the order detail info in each purchase order is the same as in the customer order, just sorted by supplier, and listing the supplier item codes rather than the in-house ones. Each item in the tblItems carries itemcode, supplier item code and supplier id.

The only data a purchase order needs stored is the purchase order number, date, and id of the order to which it pertains. All the rest can be queried and a report (PO) generated from that.

Thank you very much spikepl

That makes sense.

If I continue with your Order/PO example:

How do I automatically create this Purchase Order Table?
I assume if I do it your way it's not possible to have a Purchase Order ID like "OrderID-1", "OrderID-2", "OrderID-3", ... consecutive for the different suppliers in one order where the Purchase Order might include several items.

If I understand you right you're saying I should rather use something like
"OrderID-SupplierX", "OrderID-SupplierY, ... for the PO_IDs... right?

The number convention is kind of given in my problem due to historic reasons. But if it solves this issue then I guess it needs to be changed.

Do you have an example of your Order/PO Database that you can share?
Also the reports based on the queries for the Order as well as the POs are intersting for me especially due to the several items and suppliers in your example what matches exactly my situation that I am struggling with.


Thanks again.

Cheers,
Steve

 

[spikepl]

I cannot share it.


Do not confuse record keys for management of data with labels meant for human consumption. The former do not need to have anything to do with the latter. Specifically, your PO's are

tblPOs
--------
PoID (PK) (this is a database thing)
OrderID (FK from tblOrders)
PoDate
PoNUmber (this is for human consumption)


and you can have as many PO's as you wish for one Order. Presumably one PO for each supplier, whose goods are mentioned in the Order. This means that for each linei tem in the OrderDetails, you need to know who supplies the item mentioned there. That info is probably in your tblItems : ItemID, OurItemCode, SuppliersItemCode, SupplierID, SalesPrice, PurchasePrice ... etc

You can construct any human-readable PO-number that you like - it has nothing to do with the management /relations of the data, since you have access to all the data.

So when you want PO's , you just generate one for each supplier for an order. All the remaining data can be obtained by query from the OrderDetails of the particular order (and the item data in the table where you keep such things) and does not need to be stored separately.

The tricky part is to manage changes - if the customer calls and wants to add a bit to his order, or the supplier says that he only has 2 widgets in stock, out of the 4 ordered, and so the customer either is happy with the 2, or wants to skip the whole excercise. All these details depend on your workflow - ingrained workflows can be hard to change.

 

[Steve777]

I cannot share it.


Do not confuse record keys for management of data with labels meant for human consumption. The former do not need to have anything to do with the latter. Specifically, your PO's are

tblPOs
--------
PoID (PK) (this is a database thing)
OrderID (FK from tblOrders)
PoDate
PoNUmber (this is for human consumption)


and you can have as many PO's as you wish for one Order. Presumably one PO for each supplier, whose goods are mentioned in the Order. This means that for each linei tem in the OrderDetails, you need to know who supplies the item mentioned there. That info is probably in your tblItems : ItemID, OurItemCode, SuppliersItemCode, SupplierID, SalesPrice, PurchasePrice ... etc

You can construct any human-readable PO-number that you like - it has nothing to do with the management /relations of the data, since you have access to all the data.

So when you want PO's , you just generate one for each supplier for an order. All the remaining data can be obtained by query from the OrderDetails of the particular order (and the item data in the table where you keep such things) and does not need to be stored separately.

The tricky part is to manage changes - if the customer calls and wants to add a bit to his order, or the supplier says that he only has 2 widgets in stock, out of the 4 ordered, and so the customer either is happy with the 2, or wants to skip the whole excercise. All these details depend on your workflow - ingrained workflows can be hard to change.

Hi spikepl,


Thank you.

Yes, I definitely need to structure the Keys/Numbers better.

Does that mean in your example that I need to add to the PO table you listed as well the Supplier_ID in order to know to which supplier under an Order the PO belongs to?

Cheers,
Steve