Service Ticket Table(s) (1 Viewer)

[mike60smart]

Hi Mike,
Yes, every time we buy a new batch of devices (10 to a box) we record the device information into a table, tblPayRange. Then when we add the device to a vending machine we add the information to tblPayRangeAssignment and it's associated with a MachineID.

It just happened today. Device ID 10076330 PIN 8012 was removed from MachineID 312 on 7/24/2025 and installed into MachineID 1046 on 8/21/2025.

Every Monday we download two different reports from PayRange with details on sales and refunds. The information comes in different formats, so I download the data (it's all DeviceID centric) and put it into either tblCollectionPayRange or tblCollectionPayRangeRefunds.

I use the tblPayRangeAssignment to make sure that the proper sales and refunds are associated with the proper machine based on date range since that table has an install date and a removal date for the DeviceID.

Hope I'm explaining this in a way that is not too confusing.

No it makes complete sense

 

[mike60smart]

Hi Mike,
Yes, every time we buy a new batch of devices (10 to a box) we record the device information into a table, tblPayRange. Then when we add the device to a vending machine we add the information to tblPayRangeAssignment and it's associated with a MachineID.

It just happened today. Device ID 10076330 PIN 8012 was removed from MachineID 312 on 7/24/2025 and installed into MachineID 1046 on 8/21/2025.

Every Monday we download two different reports from PayRange with details on sales and refunds. The information comes in different formats, so I download the data (it's all DeviceID centric) and put it into either tblCollectionPayRange or tblCollectionPayRangeRefunds.

I use the tblPayRangeAssignment to make sure that the proper sales and refunds are associated with the proper machine based on date range since that table has an install date and a removal date for the DeviceID.

Hope I'm explaining this in a way that is not too confusing.

When you talk about Machine ID 312 and 1046, are these ID numbers that have been Manually created by you?

 

[LarryE]

Thank you, Larry, I'll take a look at this. I appreciate everyone's help trying to get this cleaned up so we can move forward with a cleaner/normalized database.

We're just glad you knew you need help and wanted to design it properly from the beginning. That's the biggest part. You have a lot going on here so it could take a while to get it right.

 

[wmix]

When you talk about Machine ID 312 and 1046, are these ID numbers that have been Manually created by you?

The current owner identified machines by name only, examples here of what I walked into:
AGT Drinks
AGT Candy
Northloop Office Changer
Northloop Office Pop
Northloop Office Candy
Northloop 361 Pop
Northloop 361 Candy
Smith Building 3rd Floor Pop
Smith Building 2nd Floor Pop
Smith Building 1st Floor Pop
Smith Building 1st Floor Candy
Smith Building 1st Floor Frozen

When I created the Access database I took that name list and uploaded it into a table, tblMachine, and every name was assigned an auto-number, that I called MachineID. I now use that MachineID everywhere, so I have to keep it as is, that table is part of the sale.

 

[mike60smart]

Hi
Can you send me your current "tblMachine" with actual data?

 

[BlueSpruce]

Hi
You must have a specific List of PayRangeDevices which are installed into a specific Machine.

Then depending on circumstances the Device has to be removed from 1 machine and placed into another.

Can you give me an example list of current PayRangeDevices?

The DeviceID should have a unique index so each device cannot be assigned to more than one machine.

 

[wmix]

The DeviceID should have a unique index so each device cannot be assigned to more than one machine.

Hi,
In tblPayRange the DeviceID is the PK, it's a unique serial number.

In tblPayRangeAssignment I have an auto-number PK that I use when I assign a DeviceID and MachineID based on dates.

Are you saying I should add a different PK in the main tblPayRange?

 

[BlueSpruce]

Are you saying I should add a different PK in the main tblPayRange?

No, I'm saying when you add a DeviceID in the Machines table, you cannot allow the same device in more than one Machine record. If you transfer a device from one vending machine to another, you must first remove the device from the current machine before adding it to another machine. If some machines don't have those devices installed, then that device field in those machine records would have a null value. Every time you enter a DeviceID in the Machines form, you'll have to do a self join lookup on the Machines table to see if that DeviceID already exists in another machine record. If the value is null, you allow it, if it exists in another machine record, you can use vba to remove it from the other machine and add it to the current machine your updating.

If you need to track the history of which machines a device has been installed on, then you would create a machine detail history table and store a snapshot of the machine record in it every time you change any information on a machine. However, I doubt you or the new owner would need that history? Do you wish to track the history of all repairs done to each machine? Do the machines provide any statistics of how many times each item has been purchased? How often does each machine need to be restocked? Whats the best route for restocking the machines?

 

[mike60smart]

Hi,
In tblPayRange the DeviceID is the PK, it's a unique serial number.

In tblPayRangeAssignment I have an auto-number PK that I use when I assign a DeviceID and MachineID based on dates.

Are you saying I should add a different PK in the main tblPayRange?

Hi
Attached is a screenshot of what I think are the required Relationships. Not all tables are as yet included.

 

[LarryE]

Here's an updated design proposal. I still don't know what all the tables are for, but you can connect the ones not related. I deleted some of the tables not needed.

 

[wmix]

No, I'm saying when you add a DeviceID in the Machines table, you cannot allow the same device in more than one Machine record. If you transfer a device from one vending machine to another, you must first remove the device from the current machine before adding it to another machine. If some machines don't have those devices installed, then that device field in those machine records would have a null value. Every time you enter a DeviceID in the Machines form, you'll have to do a self join lookup on the Machines table to see if that DeviceID already exists in another machine record. If the value is null, you allow it, if it exists in another machine record, you can use vba to remove it from the other machine and add it to the current machine your updating.

If you need to track the history of which machines a device has been installed on, then you would create a machine detail history table and store a snapshot of the machine record in it every time you change any information on a machine. However, I doubt you or the new owner would need that history? Do you wish to track the history of all repairs done to each machine? Do the machines provide any statistics of how many times each item has been purchased? How often does each machine need to be restocked? Whats the best route for restocking the machines?

For PayRange the new owner gets all the devices, so he will have a list of all devices we own along with where those devices are (DeviceID and MachineID association). He does not get any of the device history (sale or refund data).

We do track all the service history on a machine via the MachineID. The MachineID drives the majority of my tables. We track when machines are stocked, what truck stocked them, what's put in them, what's taken out of them for expired products, etc. That history the new owner will get for the previous two years.

 

[wmix]

Here's an updated design proposal. I still don't know what all the tables are for, but you can connect the ones not related. I deleted some of the tables not needed.

Thank you. I've been swamped last night and today so I'll have to look this all over next week (stocking and inventory day).