Issue regarding database design and corresponding operations in forms

[CJ_London]

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

they are abbreviations, but they are aliases. Always required when you have the same table/query in a query more than once so the query engine knows which 'version' of the table/query you are looking at, but also cuts down typing and makes the code easier to read.

Many times I have seen someone name a query something like

'data received from joe smith for January'


you can imagine what any query looks like....

So the 2nd query will provide me a list of all unique units and their buy/sell operations

yes

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?

I don't see the need to base the query on the second query to assign a service event, just assign it via customer>orderheader>orderdetail, or if that is not available create a dummy supplier order as before, then dummy customer order (or another form of document 'return order'). In time they will all be registered and your data will be consistent. However that may not be practical, in which case your service table could not have referential integrity with the orderline table and you would need a different query to identify those products/serialno which are not in the orderline table (i.e. left join tblServices to tblOrderLines) - a situation a bit like the scenario I outlined in the last para of post #22

[Thomasso]

Thank you Ajax, I got the model running, now I am trying to implement the limit to a serial number to only be a part of 1 supplier order and 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'

Where do you recommend to put that code? Into the Orders Form? Or is there a way to run the code when someone tries to save a new record in a table?

Also I don't quite understand your line of code. You want to count records within tblOrderLines, where SerialNo is what exactly? What is the ampersand doing there? And why should it be equal to zero?

Thank you,
Tomas

[CJ_London]

in the serialno control on the orderline form

You want to count records within tblOrderLines, where SerialNo is what exactly - where serialno=the value in the serialno control on the form (you may have called the control txtSerialNo for example

looks like I didn't get the criteria right it should be

SerialNo='" & SerialNo & "'"

If it returns 0 then there isn't an existing record with that serialno, so it is OK for you to add it.

Google the dcount function to find out how it works

[Thomasso]

I still don't understand the quotes. Is there ' and " in the first one and " ' " in the second one? I am quite lost in this. I already read this link yesterday:

https://support.office.com/en-us/art...a-11a64acbf3d3

So I know how the function works but can't really grasp your specific definition.


Also, I decided to include carrier companies (like DHL, FedEx) in the database and I am thinking about whether I should create a new company type "Carrier" or rather whole new table.

I am inclined to go with the second option because I think that I need to store some specific data about them and connect them to Packages (another new entity I will be using). Sometimes a supplier doesn't ship the entire order at once. Also, a unit can be sent by many packages over the time and I think it would make a mess with limiting them to one S.Order and C.Order. But on the other hand, I would like to store our contacts within that carrier and then it would be impossible to store those people in tblPeople because that table is connected to tblCompanies. What would you recommend?

You don't have to answer, you helped me a lot already and I took a big amount of your time.

Cheers,
Tomas

[CJ_London]

Is there ' and " in the first one and " ' " in the second one?

yes. SerialNo is a string and strings always need to be identified with single quotes

There are a couple of ways you can handle deliveries:

For the carrier, I would suggest if you are storing the same sort of detail as for customers and suppliers (they are a supplier as such) then include in your companies table - and add a 'Carrier' record to the company types table.

If orders can be shipped over several shipments, you will need another 'deliveries' table (is this your packages?). This would be linked either to your orderheader table (if a delivery is only ever from one order) or the companies table twice (if one delivery can cover more than one order), once to the customerPK and a second field to the supplierPK and contain references and date. You can then either create its own 'deliverylines' table to list each item being delivered (which would store the PK of the records in the orderlines table), chosen by filtering on the orderheader table for a specific order or orders from a particular customer and further filtering to exclude those items already delivered. Or you add a delivery field reference to the orderliness table which links to the deliveryheader table. Which is better for you, you will need to decide.

In relationships design, you can drag a table onto the grid more than once - the second time it will be aliased with a _1 suffix. So drag tblCompanies on twice, one will link to delivery header customerFK field, and the second to the SupplierFK field.

There is no need to drag a second copy of the companytypes table onto the grid and join to tblCompanies_1 since the relationship has already been defined

Note that relationships are not the same as query joins. They look the same (in the same way queries and tables look the same in datasheet view) but are completely different under the hood.