4

I work for a manufacturing company which sells machines to customers which have a control unit installed in them. We need to keep track of the locations of all the Machines after they are sold to the Customers. We need to keep track of all the Control Units whether they are assigned to the Machines, they are sent to Customers as spares, they have being returned back to the Supplier for repair or in stock. I am trying to setup tables to record of the history of the Locations of Machines which can be assigned to a Customer but then the same machine can be moved/reassigned to another Customer. The Machine has a Serial Number. Also need to record the history of the Locations of a Control Unit which can be assigned to a Machine which can then be moved/reassigned to another Machine. A Control Unit can also be sent to a Customer as a backup unit in case of failure or it can be sent to the manufacturer for repair. After the Control Unit comes back from the Manufacturers it is put back in stock and at some stage it can be reassigned to another Machine. The Control Unit also has a Serial Number.

I have attached an ER Diagram of what I think might be a suitable solution. The ERD diagram shows the link to MachineID, CustomerID and ManufacturerID in the TBL_ControlUnitLocations table. The problem I see is that when you select which link you want, the other two will need to be made NULL. When you view the history on a particular Control Unit you will end up with two fields always being blank.

One option is not to show the MachineID, CustomerID and ManufacturerID fields on the main form and only show the Location field. Is it possible to have another form which pops up and gives you the option to choice which one of the MachineID, CustomerID and ManufacturerID fields you wish to link to and then assign the link to the Location field and then close the popup form. So all you see then is the Location field.

How would you implemented the main form and the popup form to record the association? enter image description here

3
+100

What you're looking for is a VIEW. Here is a link for more description.

https://msdn.microsoft.com/en-us/library/office/ff836312.aspx

This is what it will look like, and you can create a form around the view.

CREATE VIEW vw_cust_mach_loc
AS
SELECT cust.CustomerName,
   cust.CustomerAddress,
   cust.SuburbTownCity,
   cust.State,
   cust.PostCode,
   cust.Country,
   cust.FirstName,
   cust.SurnameName,
   cust.PhoneNumber,
   cust.MobileNumber,
   cust.EmailAddress,
   cust.Notes,
   m_lo.CustomerReference,
   m_lo.TimeStamp,
   m_lo.StatusID,
   m_lo.OriginalCustomer,
   m_lo.Notes,
   mach.MachineSerialNumber,
   mach.MachineModeID,
   mach.MachineOtherFields,
   mach.Notes
FROM TBL_Customers cust
   INNER JOIN TBL_MachineLocations m_lo
       ON cust.CustomerID = m_lo.CurrentCustomerID
   INNER JOIN TBL_Machines mach
       ON m_lo.MachineID = mach.MachineID;

CREATE VIEW vw_cust_mach_sup_ctrl_units
AS
SELECT cust.CustomerName,
       cust.CustomerAddress,
       cust.SuburbTownCity,
       cust.State,
       cust.PostCode,
       cust.Country,
       cust.FirstName,
       cust.SurnameName,
       cust.PhoneNumber,
       cust.MobileNumber,
       cust.EmailAddress,
       cust.Notes,
       mach.MachineSerialNumber,
       mach.MachineModeID,
       mach.MachineOtherFields,
       mach.Notes,
       culs.Location,
       culs.Reference,
       culs.TimeStamp,
       culs.OriginalMachine,
       culs.OtherLocationID,
       culs.StatusID,
       culs.Notes,
       supl.SupplierName,
       supl.ContactFirstName,
       supl.ContactLastName,
       supl.ContactTitle,
       supl.Address,
       supl.City,
       supl.Region,
       supl.PostalCode,
       supl.Country,
       supl.PhoneNumber,
       supl.MobileNumber,
       supl.Fax,
       supl.[E-mail Address],
       supl.HomePage,
       supl.Notes,
       cuts.ControlUnitSerielNumber,
       cuts.ControlUnitBarCodeID,
       cuts.ControlUnitStatusID,
       cuts.TimeStamp,
       cuts.Notes
FROM TBL_Customers cust
     INNER JOIN TBL_ControlUnitLocations culs
         ON cust.CustomersID = culs.CustomerID
     INNER JOIN TBL_Machines mach
         ON culs.MachineID = mach.MachineID
     INNER JOIN TBL_Suppliers supl
         ON culs.ManufactureID = supl.SupplierID
     INNER JOIN TBL_ControlUnits cuts
         ON culs.ControlUnitID = cuts.ControlUnitIDMachineID;

You can select from it like a table.

SELECT * FROM vw_cust_mach_loc;
SELECT * FROM vw_cust_mach_sup_ctrl_units;

Example using specific columns instead of *:

SELECT
    Location,
    Reference,
    TimeStamp,
    OriginalMachine,
    OtherLocationID,
    StatusID,
    Notes,
    SupplierName,
    ContactFirstName,
    ContactLastName,
    ContactTitle,
    Address,
    City,
    Region,
    PostalCode,
    Country,
    PhoneNumber,
    MobileNumber,
    Fax,
    [E-mail Address],
    HomePage,
    Notes,
    ControlUnitSerielNumber,
    ControlUnitBarCodeID,
    ControlUnitStatusID,
    TimeStamp,
    Notes
FROM vw_cust_mach_sup_ctrl_units
  • Thanks for your answer Tigerjz32. Does this have to be implemented via code in a form or can you create a query which allows you to update the tables – Bruno Aloe Sep 26 '18 at 5:46
  • I am not too familiar with MS Access but I think you can create this view as a datasource and just use it like you would use a table to create forms. I will let you know if I find some more details on this answer. – Tigerjz32 Sep 26 '18 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.