Hi Everyone,
I'm trying to automate a process I currently do manually and need some help.
For vending machines, we track all the money in/out of the machine. I am specifically looking for our "CHANGER" machines. These are the one-to-one coin machines that return money, example, something you see in a laundry mat where you put in a $1 bill and get $1 worth of quarters.
Based on two different tables I created one query that tells me the following:
qryActiveChangerCollections
CollectionID
MachineID
CollectionDate
CashRcvd (the money the driver brings back to the office to be counted)
CoinsRtrnd (the money we give to the driver - coins - to add to the machine)
CollectionNotes
Here's a small example of what I see when I pull this query (there are thousands of entries):
With all this data I can run the "balance" of a specific machine. Here's the problem, we have years worth of data on each machine. Honestly, I don't need years worth of data. When I need information it's always from a specific date forward. The START date on a machine.
Here's where I'm running into issues, the START date on a machine varies by machine.
For example, machine # 845 above is a brand new machine that just went into service. However, machine # 99 above has been in service for over 15 years. So that machines "START" date is very different. This machine has a start date of 4/3/2020. Because on that date we "batched out" the machine. We took all the money out of the machine, cash and coins, added it all up and made sure the machine "balanced" and we started the machine over. When this happens we go to the original "start" and change it to "end" and add a note like "END - batched machine, customer complaint".
So, when I need to do calculations on data it needs to be from the START of a machine through present date.
Is there any way to accomplish what I'm trying to do? I have created and deleted so many different queries my head is spinning (this has been an issue I've toyed around with solving for over a year now). So, currently I manually query the MachineID I want and then move the data to Excel where I sort and do all my calculations. It just seems to me that with as powerful as Access is that I'm missing something.
It was suggested to me to add a "check box" to my CollectionID. If the box is checked then I don't add that data. I hate the idea of manually sitting and making these changes but if it will work I'll give it a shot. Honestly, I was really hoping I could use the word START as my indicator and not the checkboxes which to me will just add another column to my table. Thoughts?
I appreciate any guidance you can provide.
I'm trying to automate a process I currently do manually and need some help.
For vending machines, we track all the money in/out of the machine. I am specifically looking for our "CHANGER" machines. These are the one-to-one coin machines that return money, example, something you see in a laundry mat where you put in a $1 bill and get $1 worth of quarters.
Based on two different tables I created one query that tells me the following:
qryActiveChangerCollections
CollectionID
MachineID
CollectionDate
CashRcvd (the money the driver brings back to the office to be counted)
CoinsRtrnd (the money we give to the driver - coins - to add to the machine)
CollectionNotes
Here's a small example of what I see when I pull this query (there are thousands of entries):
CollectionID | MachineID | CollectionDate | CashRcvd | CoinsRtrnd | CollectionNotes |
116486 | 786 | 7/7/2020 | $123.00 | $0.00 | |
116435 | 357 | 7/6/5020 | $60.00 | $0.00 | |
116423 | 244 | 7/6/2020 | $25.00 | $0.00 | |
116378 | 413 | 7/6/2020 | $686.00 | $0.00 | |
116294 | 307 | 7/3/2020 | $0.00 | ($500.00) | Returned for week of 7/6 |
116217 | 487 | 7/2/2020 | $138.00 | $0.00 | |
115923 | 845 | 6/26/2020 | $0.00 | ($500.00) | START |
115541 | 99 | 6/19/2020 | $0.00 | ($1000.00) | Returned for week of 6/22, 2 bags |
With all this data I can run the "balance" of a specific machine. Here's the problem, we have years worth of data on each machine. Honestly, I don't need years worth of data. When I need information it's always from a specific date forward. The START date on a machine.
Here's where I'm running into issues, the START date on a machine varies by machine.
For example, machine # 845 above is a brand new machine that just went into service. However, machine # 99 above has been in service for over 15 years. So that machines "START" date is very different. This machine has a start date of 4/3/2020. Because on that date we "batched out" the machine. We took all the money out of the machine, cash and coins, added it all up and made sure the machine "balanced" and we started the machine over. When this happens we go to the original "start" and change it to "end" and add a note like "END - batched machine, customer complaint".
So, when I need to do calculations on data it needs to be from the START of a machine through present date.
Is there any way to accomplish what I'm trying to do? I have created and deleted so many different queries my head is spinning (this has been an issue I've toyed around with solving for over a year now). So, currently I manually query the MachineID I want and then move the data to Excel where I sort and do all my calculations. It just seems to me that with as powerful as Access is that I'm missing something.
It was suggested to me to add a "check box" to my CollectionID. If the box is checked then I don't add that data. I hate the idea of manually sitting and making these changes but if it will work I'll give it a shot. Honestly, I was really hoping I could use the word START as my indicator and not the checkboxes which to me will just add another column to my table. Thoughts?
I appreciate any guidance you can provide.