Sequential Primary Key generation across multiple tables

[Dal Jeanis]

Hmmm. What, precisely, do you mean when you say that "each test can have many parameters"?

* "I will perform this test, and measure these twelve items, each of which has specific tolerances"?
* "I will perform this test with parameter 1, and measure the results. Then I will perform this test with parameter 2, and measure the results..."

This requirement sets off warning bells: "The amount of a raw item lot used to form a construct must be recorded."

So far, you have not tried to deal with the amount of materials that come in, are destroyed or used. What is the real meaning of that statement?
* "We must have a text field that allows entry of the mix amounts."


* "We must have a table that allows tracking of amounts and proportions that made up a construct."
* "We must be able to fully track the usage of the raw materials."

[rpeare]

in my suggested build your tblItems is storing both raw materials and constructs, the link to the tblConstructs shows which raw items feed into the constructed item. There's no reason to have raw items and constructs on different tables. So for instance let's say Composition A is made of Item A and Item B and Item C in a 25%, 30%, 45% ratio. then it doesn't matter how large a batch you're making, You'd just have to know the final weight (or volume) you wanted to end up with and get the correct amounts, then record the usage in a different table. If your intent is not only to have a functioning inventory control system in this database you do, indeed, have to have some method to record sales, usage for making compositions, measuring scrap AND a method of handling inventory adjustment when inventory counts happen at the end of the month/quarter.

But again, all of the usage can be stored in a single table regardless of where the usage comes from. It would be really helpful if you not only recorded incoming invoices but created your own so all your usage could also be in your table that stores incoming data.

[EdaxFlamma]

To answer Dal Jeanis's first question, the first option sounds most appropriate:

* "I will perform this test, and measure these twelve items, each of which has specific tolerances"

For instance, the fields for my pH test would be the pH reading and the date the test was completed. For my moisture test, the fields would be pan number, pan mass, pan and sample mass, pan dry sample mass and date completed. I won't be changing variables within a test.

For your second question, the bottom two choices ring the most true. I feel as though the second option is implied in the third option so I will say the latter.

I wonder if it would be wise for me to cement the inventory aspects of the database and then revisit the testing and quality control stuff after I have the core in place. I welcome your advice as to where you might suggest I begin again on this project if I have in fact put the cart before the horse.

rpeares final thought there is what I had in mind. Ultimately, the database would be tracking the raw materials coming in, including cost, suppliers etc. as well as the different products being made with them, the performance of these products, the sale and shipment of those products, and quality control all around.

The biggest reason why I did not start with the inventory section of the database was because some of our products become components for other products. So not only would we make batch 001 using X amount components B,C, and D, we would also need to use X amount of batch 001, and components A,B,C to make batch 002. The cyclical aspect of this and my level of expertise made my shy away from that as a starting point.

[Dal Jeanis]

Okay, glad I asked. There was someone else a few days back who was developing an inventory system to track incoming and outgoing parts, that were going to be processed and subjected to destructive testing. I'd suggest you review that design discussion, rather than starting from scratch.

Warning: It's long. https://www.accessforums.net/databas...ign-35245.html

The key here is, don't get overwhelmed by all the details, either of his application or of your needs. It may help to design and implement a subset of the functionality of your overall end state system, get it working, more or less, then design and implement a different subset in a new database, and then design and implement your endstate system by reusing working pieces of the two systems. Prototyping can get you over a lot of humps.

The factors that you discussed on this thread were doable, up until you said you needed to track the amount of materials that went into a combo. Then your predicted needs began to asymptotically approach an entire functioning ERP system.

Take a look at that other thread, think about your business needs, and see if you can come up with an initial requirements subset that gives you valuable capabilities but doesn't require you to rewrite Adaxa or SAP from scratch.

[EdaxFlamma]

I will definitely look into this. Thank you very much!

I do understand that the end goal of the project is rather lofty... Perhaps another reason why I held back some details of the project. I think after reviewing the material that you suggested I will have a few more tools in my belt to chip away at it the pieces.

The databases that we have will certainly keep us going in the mean time. Dal Jeanis and rpeare thank you both for the help! If you have any other suggestions I am all ears.

[rpeare]

If you want my opinion (and who doesn't, right?) I would work on the inventory control part first. that would be the recording of incoming inventory and the recording of outgoing inventory. This would be both the receipt and distribution through PO's from suppliers to customers. Your PO tables should be able to accommodate both. Once you have that working you can try to work in your inventory adjustments for scrap/waste/month end inventory adjustments. Very likely this can also go on your PO detail table there would just be no associated PO. This in itself is a fairly major project and I suspect once you have a working system here you'll find it much easier to work in your testing information. I would not recommend working on the testing first, I know that's your goal but the inventory management portion is much more important to get right.

[EdaxFlamma]

Haha that was what I was afraid of... I will do my best to square up the inventory side then.

You guys have been a wealth of information. Back to the drawing boards!

Cheers,
-J.P.

[EdaxFlamma]

@rpeare: Could I ask again for a little bit more clarification on posts #17 and #14?

Post #17 had a lot of ideas in it, all good stuff! I just want to be sure I am on the same page.

"It would be really helpful if you not only recorded incoming invoices but created your own so all your usage could also be in your table that stores incoming data.​"

Are you suggesting a sole transactions table for incoming and outgoing invoices? If so then great I'm on board! (see updated relationships below)

"If your intent is not only to have a functioning inventory control system in this database you do, indeed, have to have some method to record sales, usage for making compositions, measuring scrap AND a method of handling inventory adjustment when inventory counts happen at the end of the month/quarter."

This usage data, assuming the usage of an all inclusive "tblTransactions", would be dealt with via the forms and queries correct? Scrap and necessary additions/subtractions would also all be dealt with through the transactions table and associated forms for the various transaction types. Construct usages I would imagine could be obtained via the stored values for each construct. Am I correct? I added a stocktake table as well.

"In my suggested build [Post #14] your tblItems is storing both raw materials and constructs, the link to the tblConstructs shows which raw items feed into the constructed item. There's no reason to have raw items and constructs on different tables."

I can see the connection there certainly but I think my poor execution is preventing me from seeing how it all fits together. If the table is set up as you described in Post #14 and I needed to reference one of the items in the construct and see it's relevant info would I be able to? The lack of link back to the ItemID table makes me think I couldn't query based on the ItemID in the subtable. I fear this also might

"So for instance let's say Composition A is made of Item A and Item B and Item C in a 25%, 30%, 45% ratio. then it doesn't matter how large a batch you're making, You'd just have to know the final weight (or volume) you wanted to end up with and get the correct amounts, then record the usage in a different table."

This is the part where I get lost. I thought usage was being stored in the subtable? Personally I'd rather keep track of the actual amounts as they can change rather frequently but I don't see that being a problem. I just wasn't sure about the "different table" you suggested.

Thanks again.

[rpeare]

Can you tell me what you're trying to accomplish with your customer tables and your supplier tables, again it's basically the same information you're keeping in 6 different tables.

At the very least (depending on what you're using them for) your tblCustomerID can be combined with 1SupplierID
tblSupplierAddress can be combined with tblCustomerAddress all you need is a flag saying whether or not the company is a supplier or customer (if a company can be both you can do that too)

From your setup a supplier or customer can have multiple addresses but I have no idea what you are doing with your tables tblCustomerAddressID and tblSupplierAddressID it looks like it's a junction table but I can't see any reason you'd need it you should just be linking your tblCustomerID to tblCustomerAddresses if you have multiple shipping addresses for both customers and suppliers. Likewise, what is your 1DistribuorIDs table supposed to be doing? it's got the same basic setup as tblCustomerID but no 'supporting' tables.

Do not tackle your constructed items yet (in terms of usage).
When the time comes for creating batches the Raw Items and Constructed Items will be in your tblItems
The construction ratios (assuming they are constant regardless of batch size) will be in another table and won't be used for anything other than displaying what the recipe SHOULD be made of.
The actual weight/volume of materials used will be entered into your tblTransactionDetail (or if you don't feel comfortable with that another non PO related table)

What is your tblStockTake supposed to be doing?

For right now I would focus on minimizing the tables you're using, combine your customers and suppliers into one table. IF your suppliers and customers can have multiple addresses have the shipping addresses in a second table linked to the CompanyID. As far as I can see the junction table tblCustomerAddressID is useless.

get your tblItems to show your full list of both raw materials and constructed items.

Build your data entry screens for:

Company (this would be your tblCustomerID/tblSupplierID, your distributors could/should be on this table as well and if one company can be all three that should be accounted for as well tables combined)
CompanyShipping (this would be your tblCustomerAddress/tblSupplierAddress tables combined, just make sure it has a FK to the Company table)
most likely this will be a form/subform arrangement but there are a bunch of ways to do it
Items
Purchase Orders (incoming and outgoing)

Get this part working first. It's going to be the driving force for everything else you build.

[EdaxFlamma]

Each transaction is involving one party receiving a product and one delivering a product. I did have them combined in one attempt but I then when I tried to link it to the transaction table I had a duplicate tblCompany (suppliers+customers) which I wasn't something I was used to seeing so I figured for the sake of simplicity I would show it in a separate set of tables.

I agree wholeheartedly that I don't need that junction table. I'm still trying to figure out why I put that in originally - my idjit card for the day.

My tblDistributor is there because we have a distributor that deals with certain customers and certain products some of the time. We need to keep track of whether or not a sale was made by us or through our distributor for our sales and accounting team.

tblStockTake was taken from here: http://allenbrowne.com/AppInventory.html We have raw products that can get used over the course of a year or longer from which calculating inventories from scratch might be a bit cumbersome. Additionally we do a stock take at least every month that we can compare to the digital inventory as a fail safe.

In the mean time I will try to get a proof of concept up an running and see where that takes me. I will continue to check back in case there are any responses or suggestions. Thank you very much for your help, I really appreciate it,

[Dal Jeanis]

Repeat after Peewee Herman - "I meant to do that."

[rpeare]

Ok, the stocktake I can understand, but it's a process separate from what I mentioned in my previous post, so my advice is unchanged, get the PO part of your application working first. Everything else flows from that.

[EdaxFlamma]

And just to double check, in this case it is fine to have multiple links from my tblCompany to my tblTransactions right? CompanyID being the fusion of Suppliers, Customers and distributors.
CompanyID --> SupplierID
CompanyID --> CustomerID
CompanyID --> DistributorID

[rpeare]

I don't know how your company operates.

If you have a system in place that gives each supplier, customer and distributor a number (for instance any distributor gets a distributor number of DXXXXXX) you should preserve that, but that would NOT be your PK, I'd give each supplier, customer or distributor a PK (autonumber) of it's own if you do have an internal numbering system.

Let's say you have
CompanyA, company A can be either a distributor or a customer

Your table would look something like:

tblCompanies
CompanyID  CompanyName  Distributor  Customer  Supplier
1          CompanyA     -1           -1

where distributor, customer and supplier are yes/no fields.

if a company can be one and only one of the three types all you'd need is an indicator in a single field

tblCompanies
CompanyID  CompanyName  CompanyTypeID
1          CompanyA     1

tblCompanyType
CompanyTypeID  CompanyType
1              Customer
2              Supplier
3              Distributor

and if your internal ID exists you'd have to have a field for that value of course.

[EdaxFlamma]

We do not currently have an internalized numbering system so we could just use the autonumber and the yes/no fields would be very helpful.

I still feel like that you would need multiple connections from the tblCompany to the tblTransaction. Is my thinking correct here or am I being stubborn and missing something obvious?