Need to store units that we did not sell in the first place but only do repairs

[Thomasso]

Hello,

my company is a B2B one and we're storing each unit sold by its serial number for purposes of monitoring repairs, calibrations etc.

The current architecture is as follows:

I've got orders divided to:
- our orders from the suppliers (S)


- orders placed by our customers (C)

In order for a unit to show (with its S/N), I have created this query:



The S and C tables are actually 2 copies of the OrderDetails table, one filtered to Supplier Orders (S) and Customer Orders (C). The query shows various information like the product model, the customer, buying price, selling price, profit, etc.

The problem is that we have been using the database for only a year or so. But the company has been on the market for 30 years. We receive many RMAs (repair inquiries) with products that we sold ages ago. Therefore they are not in the database. Or we might have not sold some unit at all.

I created a dummy customer order for a fictional customer (ourselves) where I put all such items.

In the main menu of the database, I have a text box where I type a serial number, press a button and the database shows all the information about that particular unit.
- when it was bought, sold, for how much, to whom, all the repair and calibrations...
The form's data source is the query above.

The problem is that it only shows units that we ordered from our suppliers (see the joins). I need it to show units that are ONLY in the customer orders but at the same time I want to keep showing the units only in the supplier orders. Is this possible to achieve?

Thanks for any tip.

Best regards,
Tomas

[Minty]

If you just queried the whole table for that serial number and displayed the source as a calculated field (C or S) you would get all your records with an identifier - would that help?
The other option would be a union query , but as the records are all in the same underlying table that seems pointless.

Edit the query to display all the C/S records and only match the product ones that match - e.g. change the join around.

[ranman256]

you want an OUTER join from the customer to the product.
show all records in C, some records in tblProd

products should not come from S, but rather from tProd.
the tProd.serial will determine the S.

[ssanfu]

You should probably rename your fields - there can only be 1 PK field, so the bottom 4 fields have the wrong prefix. (should be FK, not PK)

Hopefully you have one table for "OrderDetails" (with a field for "Supplier" or "Customer"), linked to table "tblProdukty" on "tblProdukty.PKProdukty" = "OrderDetails.FKProdukty".

But I am confused..... maybe there are two copies of the table "OrderDetails" aliased with S and C??? Hard to tell without seeing theSQL or the dB......


I don't think there should be a link between S.SerialNumber and C.SerialNumber.

[Thomasso]

You should probably rename your fields - there can only be 1 PK field, so the bottom 4 fields have the wrong prefix. (should be FK, not PK)

Hopefully you have one table for "OrderDetails" (with a field for "Supplier" or "Customer"), linked to table "tblProdukty" on "tblProdukty.PKProdukty" = "OrderDetails.FKProdukty".

But I am confused..... maybe there are two copies of the table "OrderDetails" aliased with S and C??? Hard to tell without seeing theSQL or the dB......


I don't think there should be a link between S.SerialNumber and C.SerialNumber.

Yes, that's true. However, that's just a technicality that's not affecting the functionality of the database.

I understand that Czech language might be confusing... The database sctructure is like this:
- Orders Table (stores information about the order itself... Customer, date etc.)
- Order Details Table (stores units)

When an order is being created, it depends on which company I select - supplier or customer. There are 2 queries which show all the supplier orders and all the customer orders and then I work with that further.

I think that the S.SerialNumber and C.SerialNumber link is fine because I need to store unique units and then be able to display information of how it was bought and how it was sold. Now repairs as well. Each serial number can be used exactly once in S and once in C. I have this all set up along with error messages (This S/N is already part of another supplier order... Etc.).

The ones that are only in S and not in C, those are the items that we have in the house.

Yes you are right, they are 2 copies of the table.

Table Products (Produkty in my language) stores product types... For example I can have Samsung Galaxy S7 Edge, that's in the Products Table, and if I have 3 units with S/N 001 002 and 003, that's stored in the Order Details Table (twice if sold by us, once in S and once in C) and it's connected with Products Table so I know it's the Samsung.

[Thomasso]

If you just queried the whole table for that serial number and displayed the source as a calculated field (C or S) you would get all your records with an identifier - would that help?
The other option would be a union query , but as the records are all in the same underlying table that seems pointless.

Edit the query to display all the C/S records and only match the product ones that match - e.g. change the join around.

you want an OUTER join from the customer to the product.
show all records in C, some records in tblProd

products should not come from S, but rather from tProd.
the tProd.serial will determine the S.

Thanks guys, I will give that a go and report back :-)

[Thomasso]

If you just queried the whole table for that serial number and displayed the source as a calculated field (C or S) you would get all your records with an identifier - would that help?
The other option would be a union query , but as the records are all in the same underlying table that seems pointless.

Edit the query to display all the C/S records and only match the product ones that match - e.g. change the join around.

Hello, I tried your solution and it works like you said but it doesn't quite solve my issue. I actually need it to display all the possibilities, including:
- units that have been bought but haven't been sold (they're in stock)
- units that were sold but haven't been bought (a simulation of the items that we didn't sell and received for repairs)
- units that have been both bought and sold

A very simple example how that could look like: (excel file with description attached)

Let's say that I order 2 items from a supplier within 1 order. Those items would be product model 1 with S/N 111 and product model 2 with S/N 222 (first 2 records in tblOrderDetails). Both items were bought for 10$.

After that a customer buys one product from us, the model 1 with S/N 111, for 20$ (third record in tblOrderDetails).

The next day we receive a damaged unit that we've never seen before. We are supposed to repair it as that's also a thing that we do. For this purpose I created a fake customer - our company - and I will add this unit to the database by saying that we bought it from ourselves... Unfortunately I didn't come up with another solution.

=========================================

Ok so that was the tblOrderDetails. Now the query that I need... In the case above it should look like the query provided in Excel. I only want to display each unit once with all respective available information.

=========================================
This is the structure of the database relationships:

The heart of it is tblCompanies. That's all the companies that we're in contact with, both customers and suppliers. They're being distinguished by the FKTypFirmyID, where 1 means Supplier and 2 Customer (gonna add 3 for Carrier soon).

Each order is bound to 1 single company, either a customer or a supplier. I have a query for both types and it's filtered again by the FKTypFirmyID value.

The connection between tblCompanies and tblProducts is there so I am able to assign the manufacturer to each product. The combo box there is again filtered so only suppliers appear.

An old classic connection - Orders -> OrderDetails. It's also interconnected with tblProducts.

=========================================
Screenshot of the Excel file: (didn't find a file upload here)

[orange]

Seems you are dealing with these "sets" of equipment.
Units Purchased
Units Purchased and Sold
Units Purchased, Sold and Repaired
Units Repaired but not (Purchased or Sold)

[Thomasso]

Seems you are dealing with these "sets" of equipment.
Units Purchased
Units Purchased and Sold
Units Purchased, Sold and Repaired
Units Repaired but not (Purchased or Sold)

Yes, that is true. But how does this summarization solve my problem?

[orange]

It doesn't "solve" the problem but was intended to help clarify the requirement.
You have Customers and Suppliers. Which you say are companies--does that mean you don't sell to individuals? Can an individual bring in equipment for repair? You mention Orders and OrderDetails, and I suspect there are both PurchaseOrders and PurchaseOrderDetails as well as SalesOrders and SalesOrderDetails.

If you purchase products from a supplier, that could indicate a PurchaesOrder. When you sell a Product to a Customer, that could indicate a SalesOrder. When you Repair a Product, that could be a WorkOrder or Repair Order.

Seems to me a serial number should uniquely identify a Product, and via a query(s) the Customer or Supplier or Repair.

Bottom line is you may have a data structure issue, but you know your set up and readers do not.

You are correct that the Czech language could be part of the confusion.

[Thomasso]

It doesn't "solve" the problem but was intended to help clarify the requirement.
You have Customers and Suppliers. Which you say are companies--does that mean you don't sell to individuals? Can an individual bring in equipment for repair? You mention Orders and OrderDetails, and I suspect there are both PurchaseOrders and PurchaseOrderDetails as well as SalesOrders and SalesOrderDetails.

If you purchase products from a supplier, that could indicate a PurchaesOrder. When you sell a Product to a Customer, that could indicate a SalesOrder. When you Repair a Product, that could be a WorkOrder or Repair Order.

Seems to me a serial number should uniquely identify a Product, and via a query(s) the Customer or Supplier or Repair.

Bottom line is you may have a data structure issue, but you know your set up and readers do not.

You are correct that the Czech language could be part of the confusion.

You are certainly right. The requirement needs to be clarified because our business model is quite unique.

Our company is a B2B one (marketing term), or Business-to-Business. It means that our customers are other companies, not individuals. We sell hi-tech equipment which an individual a) can't afford, b) doesn't need.

Yes, an individual certainly can bring a device for repair, but he would just bring it on behalf of his/her company. I store People with FK_Company. Every person in our database must be affiliated with a company, may that be a customer, a supplier, a carrier etc.

You are right about the Orders and OrderDetails. The logic of the database is as follows:

• tblCompany has FK_CompanyType
• tblCompanyTypes has 3 values - 1 is supplier, 2 is customer, 3 is carrier (for now)
• tblOrders has FK_Company - and in the query it's filtered by the value in tblCompanyTypes
  
  • therefore I have 2 identical queries for orders, with only this difference: qryPurchaseOrders - CompanyType = 1 and qrySalesOrders - CompanyType = 2
  
  
• tblOrderDetails is normally connected via FK_Order

Repairs are currently store in tblRepairs and are handled independently of classic orders. There is a connection to the serial number though. The idea is that I type the serial number of a random products and I see purchase and sales information as well as the list of repairs and possibly other information in the future.

Seems to me a serial number should uniquely identify a Product, and via a query(s) the Customer or Supplier or Repair.

It works exactly like this to the best of my knowledge.

Thanks,
Tomas

[orange]

How would you gather and record/find the info before the database?

The problem is that we have been using the database for only a year or so. But the company has been on the market for 30 years. We receive many RMAs (repair inquiries) with products that we sold ages ago. Therefore they are not in the database. Or we might have not sold some unit at all.

[Thomasso]

How would you gather and record/find the info before the database?

We didn't.