Summarize data between dates

[jrg24]

I have a table that contains a Plate ID, cost, start date and end date, and a daily rate of amortization for each plate. Over time, new plates will be added to this data. These new records will have a different start date but the end date will always be the same.

I also have a date table that includes all dates between my first start date to the end date (exactly 365 days). In addition to the date this file contains a serialized day (The first begin date is 1, the day after the first begin date is 2, etc.), a week number determined from the initial start date, the month the day should be associated with in text form (2017-01, 2017-02, etc.), and also an internal field that connects these to a particular settlement date(this field is not a date, it is text).

My ultimate end goal would be to be able to create a data model in excel using data in Access. In terms of a pivot table what I would like to do is have plates listed in rows, months across the columns, and the summation of the daily amortization rate in the values section.

If I was doing this in excel, I could accomplish this manually by copying the plate records and duplicating it for each day between its start and end date. I assume there is a way to use queries in access to do this for me, but I am not exactly sure where to start.



I have done something similar using select queries but in my limited knowledge of access I would need to create 365 select queries (one for each date) to accomplish my goal and then union them all together. I hope there is a better way.

Any help would be appreciated.

[orange]

??? What is a plate? Perhaps you could tell us in plain English -just as you would tell an 8 yr old -- what the business is?

[jrg24]

an IRP plate is a license plate for commercial vehicles that cross state lines.
I found a way to do what I am trying to accomplish via select and union queries; but I am now running into issues in access not allowing me to paste the query into Access. I keep getting errors like "Text is too long to be edited" or "query is too complex." Below is a snippet of the query for the first few records. this is for the first 5 queries. This code repeats to 364 times in total. This query will return the result I am looking for, but access does not like the amount of code. Is there a limitation to the number of unions that can be done in one query? I have only ever used up to 8 at one time and have never encountered this error.

SELECT BBBB_2018_IRP.SUP, BBBB_2018_IRP.COST, BBBB_2018_IRP.REGIS_YR, BBBB_2018_IRP.UNIT_NO, BBBB_2018_IRP.VIN, BBBB_2018_IRP.PLATE_NO, BBBB_2018_IRP.VEH_YR, BBBB_2018_IRP.LESSOR_NAME, BBBB_2018_IRP.ORIGIN_STATION, BBBB_2018_IRP.SUP_DATE, BBBB_2018_IRP.TRUNC_PLATE, BBBB_2018_IRP.Effect_Plate, BBBB_2018_IRP.DAILY_RATE, BBBB_2018_IRP.Deduct_Begin_Wk, BBBB_2018_IRP.Replaced_Plate_No, BBBB_2018_IRP.[Replaced Date], BBBB_2018_IRP.Replaced,

((BBBB_2018_IRP.SUP_DATE)) AS Day

FROM BBBB_2018_IRP

WHERE(((BBBB_2018_IRP.SUP_DATE)) <= #3/31/2018#);

UNION

SELECT BBBB_2018_IRP.SUP, BBBB_2018_IRP.COST, BBBB_2018_IRP.REGIS_YR, BBBB_2018_IRP.UNIT_NO, BBBB_2018_IRP.VIN, BBBB_2018_IRP.PLATE_NO, BBBB_2018_IRP.VEH_YR, BBBB_2018_IRP.LESSOR_NAME, BBBB_2018_IRP.ORIGIN_STATION, BBBB_2018_IRP.SUP_DATE, BBBB_2018_IRP.TRUNC_PLATE, BBBB_2018_IRP.Effect_Plate, BBBB_2018_IRP.DAILY_RATE, BBBB_2018_IRP.Deduct_Begin_Wk, BBBB_2018_IRP.Replaced_Plate_No, BBBB_2018_IRP.[Replaced Date], BBBB_2018_IRP.Replaced,

((BBBB_2018_IRP.SUP_DATE)+1) AS Day

FROM BBBB_2018_IRP

WHERE(((BBBB_2018_IRP.SUP_DATE)+1) <= #3/31/2018#);

UNION

SELECT BBBB_2018_IRP.SUP, BBBB_2018_IRP.COST, BBBB_2018_IRP.REGIS_YR, BBBB_2018_IRP.UNIT_NO, BBBB_2018_IRP.VIN, BBBB_2018_IRP.PLATE_NO, BBBB_2018_IRP.VEH_YR, BBBB_2018_IRP.LESSOR_NAME, BBBB_2018_IRP.ORIGIN_STATION, BBBB_2018_IRP.SUP_DATE, BBBB_2018_IRP.TRUNC_PLATE, BBBB_2018_IRP.Effect_Plate, BBBB_2018_IRP.DAILY_RATE, BBBB_2018_IRP.Deduct_Begin_Wk, BBBB_2018_IRP.Replaced_Plate_No, BBBB_2018_IRP.[Replaced Date], BBBB_2018_IRP.Replaced,

((BBBB_2018_IRP.SUP_DATE)+2) AS Day

FROM BBBB_2018_IRP

WHERE(((BBBB_2018_IRP.SUP_DATE)+2) <= #3/31/2018#);

UNION

SELECT BBBB_2018_IRP.SUP, BBBB_2018_IRP.COST, BBBB_2018_IRP.REGIS_YR, BBBB_2018_IRP.UNIT_NO, BBBB_2018_IRP.VIN, BBBB_2018_IRP.PLATE_NO, BBBB_2018_IRP.VEH_YR, BBBB_2018_IRP.LESSOR_NAME, BBBB_2018_IRP.ORIGIN_STATION, BBBB_2018_IRP.SUP_DATE, BBBB_2018_IRP.TRUNC_PLATE, BBBB_2018_IRP.Effect_Plate, BBBB_2018_IRP.DAILY_RATE, BBBB_2018_IRP.Deduct_Begin_Wk, BBBB_2018_IRP.Replaced_Plate_No, BBBB_2018_IRP.[Replaced Date], BBBB_2018_IRP.Replaced,

((BBBB_2018_IRP.SUP_DATE)+3) AS Day

FROM BBBB_2018_IRP

WHERE(((BBBB_2018_IRP.SUP_DATE)+3) <= #3/31/2018#);

UNION

SELECT BBBB_2018_IRP.SUP, BBBB_2018_IRP.COST, BBBB_2018_IRP.REGIS_YR, BBBB_2018_IRP.UNIT_NO, BBBB_2018_IRP.VIN, BBBB_2018_IRP.PLATE_NO, BBBB_2018_IRP.VEH_YR, BBBB_2018_IRP.LESSOR_NAME, BBBB_2018_IRP.ORIGIN_STATION, BBBB_2018_IRP.SUP_DATE, BBBB_2018_IRP.TRUNC_PLATE, BBBB_2018_IRP.Effect_Plate, BBBB_2018_IRP.DAILY_RATE, BBBB_2018_IRP.Deduct_Begin_Wk, BBBB_2018_IRP.Replaced_Plate_No, BBBB_2018_IRP.[Replaced Date], BBBB_2018_IRP.Replaced,

((BBBB_2018_IRP.SUP_DATE)+4) AS Day

FROM BBBB_2018_IRP

WHERE(((BBBB_2018_IRP.SUP_DATE)+4) <= #3/31/2018#);

UNION

SELECT BBBB_2018_IRP.SUP, BBBB_2018_IRP.COST, BBBB_2018_IRP.REGIS_YR, BBBB_2018_IRP.UNIT_NO, BBBB_2018_IRP.VIN, BBBB_2018_IRP.PLATE_NO, BBBB_2018_IRP.VEH_YR, BBBB_2018_IRP.LESSOR_NAME, BBBB_2018_IRP.ORIGIN_STATION, BBBB_2018_IRP.SUP_DATE, BBBB_2018_IRP.TRUNC_PLATE, BBBB_2018_IRP.Effect_Plate, BBBB_2018_IRP.DAILY_RATE, BBBB_2018_IRP.Deduct_Begin_Wk, BBBB_2018_IRP.Replaced_Plate_No, BBBB_2018_IRP.[Replaced Date], BBBB_2018_IRP.Replaced,

((BBBB_2018_IRP.SUP_DATE)+5) AS Day

FROM BBBB_2018_IRP

WHERE(((BBBB_2018_IRP.SUP_DATE)+5) <= #3/31/2018#);

[orange]

Ok. You are dealing with license plates for inter-state commercial vehicles.

What exactly do you do in your business processes?
What would you/people do in a regular work day that involves these license plates?

Don't get hung up on queries, unions etc or Access. Start with a clear description of the business and the "issue(s)" you are trying to solve. Then, let's look a options to automating a solution.