Solved Add Data Based on "Start" date forward (1 Viewer)

wmix

Registered User.
Local time
Yesterday, 21:02
Joined
Mar 16, 2017
Messages
28
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):

CollectionIDMachineIDCollectionDateCashRcvdCoinsRtrndCollectionNotes
1164867867/7/2020$123.00$0.00
1164353577/6/5020$60.00$0.00
1164232447/6/2020$25.00$0.00
1163784137/6/2020$686.00$0.00
1162943077/3/2020$0.00($500.00)Returned for week of 7/6
1162174877/2/2020$138.00$0.00
1159238456/26/2020$0.00($500.00)START
115541996/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.
 

plog

Banishment Pending
Local time
Yesterday, 21:02
Joined
May 11, 2011
Messages
10,479
When this happens we go to the original "start" and change it to "end" and add a note like "END - batched machine, customer complaint".

I would add a tblBatch to keep track of your "batches". It would just need this data:

tblBatch
batch_ID, autonumber primary key
batch_Date, date, date that batch occured
MachineID, number, will hold ID of each machine that is batched

That table will let you determine what dates to use:

SELECT MachineID, MAX(batch_Date) AS BatchStart FROM tblBatch GROUP BY MachineID

If you put a record in tblBatch for when a machine goes online, the above query will give you the date to use for each machine. Bring it into the query you are trying to generate, JOIN it on MachineID and use BatchStart as criteria on the CollectionDate field and you will have what you want.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:02
Joined
Feb 28, 2001
Messages
20,224
If you do this as "transactional" records, you either add quarters (one type of transaction) or remove paper money (another type of transaction) on a specific date. There should be a date for each machine on which you added quarters and removed paper cash so that all paper cash is in the pouch and no quarters are in the pouch - for that machine.

On any one of those dates, create one more type of transaction where you add up the cash from previous sets of transactions and that total becomes the new value as of that date. Then dump the records older than that date.

If you DON'T use transactions, this would become incredibly more difficult.
 

wmix

Registered User.
Local time
Yesterday, 21:02
Joined
Mar 16, 2017
Messages
28
I would add a tblBatch to keep track of your "batches". It would just need this data:

tblBatch
batch_ID, autonumber primary key
batch_Date, date, date that batch occured
MachineID, number, will hold ID of each machine that is batched

That table will let you determine what dates to use:

SELECT MachineID, MAX(batch_Date) AS BatchStart FROM tblBatch GROUP BY MachineID

If you put a record in tblBatch for when a machine goes online, the above query will give you the date to use for each machine. Bring it into the query you are trying to generate, JOIN it on MachineID and use BatchStart as criteria on the CollectionDate field and you will have what you want.

plog THANK YOU! This worked perfect. I greatly appreciate your help. Now to work on creating the rest of the queries I need based on other tables that have this same type of START indicator.
 

wmix

Registered User.
Local time
Yesterday, 21:02
Joined
Mar 16, 2017
Messages
28
If you do this as "transactional" records, you either add quarters (one type of transaction) or remove paper money (another type of transaction) on a specific date. There should be a date for each machine on which you added quarters and removed paper cash so that all paper cash is in the pouch and no quarters are in the pouch - for that machine.

On any one of those dates, create one more type of transaction where you add up the cash from previous sets of transactions and that total becomes the new value as of that date. Then dump the records older than that date.

If you DON'T use transactions, this would become incredibly more difficult.

Thanks Doc Man for your reply. Yes, you are correct. Each CollectionID and CollectionDate coincides with paper-money in or coins-out. I think I understand what you're saying, but I'm not sure we could dump the old records. It's certainly something I had never thought about, I'll ask our owner and see how he feels about it. I appreciate your feedback.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:02
Joined
Feb 28, 2001
Messages
20,224
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.
I think I understand what you're saying, but I'm not sure we could dump the old records.

Basically, you make a "summary" entry of a specific date that is a rollup of all that went before it. Then delete everything that went before it. Thereafter, if you take the sum of the transactions, that "summary" gets included in the mix but just happens to be bigger than all subsequent entries.

Alternative: Have an "archives" table and instead of deleting the old records completely, you first do an INSERT INTO ... SELECT with a WHERE clause that looks at the "summary record" date. So you've copied the old records for safe keeping. THEN you delete the same records from the main table that you just archived into your history table.

Doing it this way means you have years of data - but only the relevant (post START) data in the main table.
 

Micron

AWF VIP
Local time
Yesterday, 22:02
Joined
Oct 20, 2018
Messages
3,472
Would you not be better off if you had the original start date, a date when you "renewed" it, and a date for when it was permanently removed from service? Maybe even repair service in/out dates? I don't think I'd be over-writing the original start date. You're losing history? At the very least, are you not losing the date it was originally put into service, thus losing how long it has been in service, let alone how long at a particular location?
 

arnelgp

once i caught a fish alive...
Local time
Today, 10:02
Joined
May 7, 2009
Messages
13,205
from the data you posted on post #1.
you need to determine each machine id, and the "start date":

first query (name it qryMachineStart):

select MachineID, CollectionDate From qryActiveChangerCollections Where CollectionNotes Like "Start*"

from this query qryMachineStart is the starting point of another Query (qryMachineBalance):

Code:
select B.CollectionID, A.MachineID,
B.CollectionDate,  B.CashRcvd,  B.CoinsRtrnd,
CCur(DSum("Nz([CashRcvd],0)+Nz([CoinsRtrnd], 0)", "qryActiveChangerCollections", "MachineID = " & A.MachineID & " And CollectionDate Between #" & Format(A.CollectionDate, "mm\/dd\/yyyy") & "# And #" & Format(B.CollectionDate, "mm\/dd\/yyyy") & "#")) As RunningBalance , B.CollectionNotes
from qryMachineStart AS A Inner Join  qryActiveChangerCollections AS B
On A.MachineID = B.MachineID  Where B.CollectionDate >= A.CollectionDate Order By B.CollectionDate ASC;
 

Users who are viewing this thread

Top Bottom