Rows for each day something is in effect

[thechazm]

Hey all it's been a while and hope everyone is doing good

I have something I am trying to figure out what would be the best way to setup multiple rows for calculating men per day usage. Here is an example of the data in a table:

ID, Class, Start Date & Time, End Date & Time
1, Excel, 1/9/16 0800, 1/11/16 1200

So that would be the one row of data but what I would like the output to be is:

ID, Class, Date, Hours
1, Excel, 1/9/16, 8
1, Excel, 1/10/16, 8
1, Excel, 1/11/16, 4

I can't seem to figure out if I should pursue queries or vba to produce the data. I can think of some ways of doing this but didn't want it to be too time consuming as it will be used to prep a report.

Thanks for your help and time in advance

 

[CJ_London]

you can't create data out of nothing. you would need a separate table to contain all calendar dates which will be used to populate your date column and the query to join your single record to each of the 3 relevant dates

not tested but your join would be something like

SELECT *
FROM tblDates INNER JOIN tblClasses ON tblDates.CalDate>= tblClasses.StartDT and tblDates.CalDate<= tblClasses.EndDT

note this join, although valid, cannot be displayed in the query grid, only in the sql window

a less efficient method would be to use a Cartesian query - something like

SELECT *
FROM tblDates, tblClasses
WHERE tblDates.CalDate between tblClasses.StartDT and tblClasses.EndDT

but has the benefit of being viewable in the query grid

 

[thechazm]

Thanks I will give that a go and see how that works. Since the data is only two weeks out from the selected date it shouldn't be to much

 

[plog]

While I think its possible to do this without VBA, I think VBA would be easier. It's that Hours field you want. To obtain the right value for it for each record you are going to need a sub-query and make a running total query to determine how many hours each day gets. If you just assigned each day the same Hours value it would be easier, but you have to keep track of how many hours are left for each record.

Might as well just use VBA, loop through every record and make a new record in a new table for every day you want a record for.

Lastly, I hope those aren't actual field names--those non alphanumeric characters are going to make coding (SQL or VBA) just a little more painful. I suggest renaming your fields to eliminate special characters and spaces (e.g. [Start Date & Time] = [StartDateTime]

 

[CJ_London]

just as easy to do in a query. not sure where the 8 hours come from - but if it is a standard 8/hrs a day except the last date which ends at 12 the calculation would be something like

Hours: iif(caldate=enddate,endtime-8,8)

 

[thechazm]

plog No those aren't real field names just an example but thanks If I could multithread in access then I probably would go that direction but I have to lean on what CL_London said that SQL is probably going to be the best route as the information will be processed faster than VBA in this case. At least that's what I am thinking right now