Issue regarding database design and corresponding operations in forms

[CJ_London]

That is not likely but we can't forbid someone from ordering products from more suppliers, so let's leave the possibility there

OK so a customer only ever orders products that come from one supplier (at least in any one order) so there is a direct relationship between customer and supplier. e.g. a customer is a Ford dealer so only orders Ford parts, another is a Volkswagen dealer and only orders Volkswagen parts - but occasionally a Ford dealer services a Volkswagen and needs the relevant Volkswagen parts.

[CJ_London]

when I know the serial number. Sometimes that might be at the time or customer's order, sometimes after the delivery comes.

So you assign the serial number, so the picker in the warehouse has to find the specific item. And at no point during this process do you personally actually see the item. What happens if the picker can't find the specific item and just picks another one? Or just misreads the number and picks the wrong one? Or you assign it but the order is not picked - it just stays on the shelf?

Since you are manually typing these in, what happens if you provide a wrong serial number when you first enter it into the system? How does the picker find the 'right' serial number?

I'm struggling to see how this part of the business actually works, it feels like there is a complete disconnect between what you are entering into the system and what is happening in reality. Perhaps explain this process in more detail. Sounds like you have two processes, one where you decide to order some parts for stock, and the other when a customer orders a part

[Thomasso]

Basically, you could say that.

In general, each supplier (there is not too many of them) provides a different kind of a solution, so some of our customers buy products from supplier 1, some from supplier 2.

[Thomasso]

It is quite a small company, there is no big warehouse. The entire system is managed my a few people. We don't get that many orders. We have few orders but each of them has a high value. Because these products are very expensive and we can't predict orders, we don't order most of them before an actual order comes.

We do have the most common devices in stock though because there is a big chance of them being ordered at some point.

The majority of orders goes like this: customer orders products from us -> we order them from supplier -> [perhaps 3 weeks delay, sometimes we get the invoice with serial numbers during this period, sometimes not] -> delivery comes (along with serial numbers) -> we dispatch the order to the customer.

If, somehow, we have everything in stock, the process goes like this: customer orders products from us -> we dispatch the order to the customer.
(this option is maybe 1 % of the time)

I know, it is an irregular type of business model, that's why I am seeking advice from a professional :-). Maybe the problem is that I understand the business so I am not telling you some important information which I consider obvious. Should I try and elaborate more?

Tomas

[CJ_London]

No I think that is probably enough

In order to maintain referential integrity, I think your table structure should be something like

tblCompanyTypes
CompanyTypePK autonumber
CompanyType text (2 records, Customer and Supplier)

tblCompanies
CompanyPK autonumber
CompanyTypeFK long
CompanyName text
...
...

tblProducts
ProducktPK autonumber
CompanyFK long - link to tblCompanies (selected by filtering on companytype=supplier)
ProductCode text
ProductDesc text
...
...

tblOrderHeaders
OrderHeaderPK autonumber
CompanyFK long - link to tblCompanies
OrderNo text
OrderDate date
...
...

tblOrderLines
OrderLinePK autonumber
OrderHeaderFK long - link to tblOrderHeaders
ProductFK - long - link to tblProducts
SerialNo text (use a combo box, set limit to list property to no if a supplier order so you can add manually, otherwise change property to yes, rowsource would be something like SELECT SerialNo FROM tblOrderLines GROUP BY SerialNo HAVING ProductFK=[ProductFK] AND Count(ProductFK)=1. This is called a cascading combo, You will probably need to investigate how to use them in a datasheet/continuous form. You would go back and edit these records when you receive the supplier invoice/delivery note to populate the serial numbers before revisiting the customer order to populate there

You will no doubt need some 'dummy orders' to populate unsold serial numbers for products in stock.

I'm suggesting merging customer and suppliers and their orders because it gives you one place to look for current status/history etc for products and serial numbers, but you can split it back out if you want. The main change would be to split companies, order headers and order lines. Then interrogation queries will be more complex

The point is, you cannot maintain referential integrity between products and serial numbers because when ordering, you don't know the serial numbers. By putting the serialNo as a field in tblOrderLines, you are maintaining it through managing how data is input.

[Thomasso]

Good morning Ajax,

thank you so much, this is great and I think that I am getting a hang of it. I do have some questions though.

1) I did some research on cascading combo boxes, however, could you elaborate more on: HAVING ProductFK=[ProductFK] AND Count(ProductFK)=1 ? I don't quite understand what that means.

2) If I understand, you're saying that I cannot enforce referential integrity between tblProducts and tblOrderLines, correct? There will be integrity between tblOrderHeaders and tblOrderDetails, yes?

3) What exactly should I imagine when you say "dummy orders"? How would that work in our workflow? How would they be stored and would it be possible for them to just stay in the background so we don't practically know about them?

But really, this is great and I cannot be more grateful for your help.
Tomas

[CJ_London]

sorry, wasn't very clear. perhaps I should have said

HAVING ProductFK=[cboProductFK] AND Count(ProductFK)=1 ? the bit in square brackets refers to the control on your form. If your rowsource was a separate query then it would be

HAVING ProductFK=forms!myform!cboProductFK AND Count(ProductFK)=1 ?

If I understand, you're saying that I cannot enforce referential integrity between tblProducts and tblOrderLines

No, I'm saying you can enforce referential integrity here - but clearly if you do, the product must exist in the product table before you can create a new record in orderlines - on the form you can use the combo 'not in list' event to trigger code to add a new entry into the product table. But if you have also set referential integrity between supplier and products then the product record cannot be created unless you assign a supplier, so you would need to include this as part of creating the product record. Not you do not necessarily need to complete all the details of the product record, just the minimum required to create the record. You can go back later to complete the record at a later time.

On cascading combo's you might find this link useful

https://www.access-programmers.co.uk...d.php?t=275155

In my work, there are many occasions where I do not want to enforce referential integrity - i.e. create orphan records where there is no parent. This might be for example where I need to import data but some parents do not exist. I would then have a routine to identify these orphans and take appropriate action to correct the situation (might be to set a parent, might be delete the record, might be to manually create a parent, or something else, just depends on the situation).

[Thomasso]

Thank you, I undestand the cbo now. And what about the dummy orders?

3) What exactly should I imagine when you say "dummy orders"? How would that work in our workflow? How would they be stored and would it be possible for them to just stay in the background so we don't practically know about them?

Another question: How should I filter the foreign key in tblProducts on companytype=supplier? Did you mean a query or is there a way to set this up within tables relationships?

Thank you.
Tomas

[CJ_London]

What exactly should I imagine when you say "dummy orders"?

For all unallocated products in stock, input the original stock order and if you don't have or can't identify the original order to a supplier, create a fake one including the serial number. Then you can pick from them as required for your customer orders

How would they be stored

ergo, in the same way as your existing orders, Then you can pick from them as required for your customer orders

would it be possible for them to just stay in the background so we don't practically know about them?

you could modify queries to exclude a dummy order, but you need to know about the products in stock otherwise they will never be picked for sale, so not sure why your would want to hide them - they will effectively be hidden anyway once all products have been sold.

Another question: How should I filter the foreign key in tblProducts on companytype=supplier? Did you mean a query or is there a way to set this up within tables relationships?

will automatically happen because you choose a supplier, then add products to them. You can identify which products belong to which supplier from the CompanyFK field. You wouldn't add a product to a customer so a customer will never be identified. If you wanted to know which products a customer buys, you would interrogate the orderheader and orderline tables.

[Thomasso]

So, I created this model... Is it what you had in mind, Ajax?



Is there any way that I can enforce each serial number to be in just one supply order and one customer order? And where you you recommend to set referential integrity and where not? What about cascading integrity?

Thank you :-)
Tomas

[CJ_London]

with regards the model, yes, you can add referential integrity to the join between companies and people if you want

Is there any way that I can enforce each serial number to be in just one supply order and one customer order?

not through referential integrity, but through code. With regards supplier order something like

dcount("*","tblOrderLines","SerialNo='" & SerialNo")=0

in the field validation property

with a validation text something like 'This serial number already exists'


for customer order, it should not be necessary since the combo a) rowsource excludes serialnos which are in the table more than once and b) they can't add a new one anyway

And where you you recommend to set referential integrity and where not?

as you have it is fine

What about cascading integrity?

what about it? Thought we had covered that in post #22

[CJ_London]

don't forget you don't always need both sides of a link to filter. for example you could have a query to list suppliers in tblCompanies which could be

SELECT *
FROM tblCompanies C INNER JOIN tblCompanyTypes T ON C.CompanyTypeFK=T.CompanyTypePK
WHERE T.Typfrmy="Supplier"

Or if you know the FK

SELECT *
FROM tblCompanies C
WHERE C.CompanyTypeFK=1

[Thomasso]

Thank you very much for every advice :-)

Another thing that I want to clear out is this... Let's say that a unit was ordered from a supplier and then delivered to a customer, so there are 2 records of this unit in tblOrderDetails.

I need this aggregated for further investigations. Is there a way to merge these 2 records together? When I query the serial number, I imagine a form to be displayed with information like:
- product was bought from this supplier on 1/1/2017 for $500
- on 1/15/2017, product was sold to this customer for $600
- this unit was calibrated 3 times, last calibration on 1/31/2018 by employee 1
- there was a problem written down on 6/1/2017 (description of it)

I know, this would be a standard form based on query. What I am missing is the procedure of making 2 OrderDetails records into 1 record so it acts as ONE unit (despite of being in OrderDetails twice).

Thank you
Tomas :-)

[CJ_London]

use SELECT Distinct or a group by query (if doing any aggregation such as summing/counting etc)

but if you want one row so you can see both

product was bought from this supplier on 1/1/2017 for $500
- on 1/15/2017, product was sold to this customer for $600

you need a series of queries

it would only take me a few seconds to create it in the query grid, but take too long to write out here but basically suggest you need a 'qryAll' which joins companytypes to companies to orderheaders and orderheaders to orderlines.

this will give you a list of all your transactions. As suggested before, you many not need the companytypes table, and us the FK in the company table instead.

then you would have another query based on qryAll - something like

SELECT *
FROM qryAll S LEFT JOIN qryAll C ON S.SerialNo=C.SerialNo
WHERE S.companyType="Supplier" AND C.CompanyType="Customer"

If you are looking for a specific serialno or product or a particular timescale, put the criteria in qryAll to reduce the number of rows it produces. You can also limit the fields brought through there as well. You can limit them further in the 2nd query if required.

[Thomasso]

I must say, I am smarter with every post you write, you are awesome, sir :-)

I expect that S and C are abbreviations for supplier and customer (in the query). Are they variables?

So the 2nd query will provide me a list of all unique units and their buy/sell operations. When I want to assign a service event or something else to a specific unit, I will create a query based on that 2nd query, which could be a "Create query" and will put that information in corresponding tables, yes?

Thank you so so much!
Tomas