Summarize data by pay periods

[breakingme10]

OK so it is early on a monday morning, my brain is exhausted from my 2 year old and I can't think straight to even know what to search for to find my answer. I am new to Access, but I'm not a novice at computers I swear. I actually use Mac a lot really and work on Apple iOS application development so Windows development is relatively new to me. My company is rather broad in terms of what all we do. I manage the self storage buildings and the water disposal trucks fuel logs as well as the water tickets to sort by lease and bill company. What the owner is wanting is for me to take the fuel cards which the driver writes their NAME, DATE, TIME, UNIT# (which is the truck #), GALLONS, ODOMETER MILES, and FUEL TYPE (dyed or clear). and sort them by the pay period. which is monday through sunday bi weekly ( ex. 12/16/13-12/29/13 and 12/30/13-1/12/14). He wants two reports one on the Driver and one on the Unit. I can't figure out how to get the report sorted to make it work for me. I've attached my database. Please someone help me figure this out.VehicleFuelLogs.zipVehicleFuelLogs.zip

[June7]

Nothing in the database defines the pay periods. Your pay periods are every 2 weeks? I see you are attempting to calculate 2-week periods and group on that. Do those calcs coincide with your pay periods? What's wrong with the results?

Options:

1. a field in fuel cards table for pay period number

2. a table of pay periods with fields PPNum, StartDate (EndDate is optional because it can be calculated)
A query can pull the pay period number for each delivery date in the fuel cards table

Date is a reserved word. Should not use reserved words as field names.

Why are dyed fuel cards in a separate table?

[breakingme10]

I am still learning the calculating with expressions stuff. So I am not sure how to get it to do the EndDate

[breakingme10]

I had dyed and clear together but I need them generated on separate reports so I moved them to their own table temporarily until I could figure out what I need to get done. The dates should be all fuelDate and then just "Date" as the caption so which table/form/etc is it that you found that had it as Date? I must have flubbed it up. The dates are the days the employee extracted the fuel from our pumps but they don't always write the time or odometer miles which is frustrating. I will start the pay period table that is something i completely overlooked! It's the first of the month and the storage renters are coming in every five seconds to pay storage bills (which is only tracked via excel and i'm in the process of trying to get that into a database as well). So i'm so busy and so stressed Thank you for taking the time out of your day to help me I appreciate it more than I can say!

[rpeare]

Some of the same things June pointed out (enclosing a modified version of your database with 2 additional reports assuming you wanted a reports for all data for the period, one sorted by driver, one sorted by tuck).

1. Don't use spaces or any other special characters in your database object names. You're using an apostrophe in one of your table field names and the ' mark is a notation that a string is to be treated as text which may cause you problems. You're using a # mark in your unit # alias for the unitnumber field on your fuel cards table. # is a notation meant to indicate date so anything enclosed between two # marks is going to be treated as a date.
2. Don't use reserved words (Date, year, left, right, etc)
3. Your table structure is not great. It probably works for what you're doing but your drivers should have a primary key field then any information associated with that driver (phone number, first and last name in separate fields, address maybe, etc) then you would only store their PK in your fuel cards table, not their name. the reason for this is... let's say you had a driver that changed their surname all your data regarding that person would not be altered it would only affect any new records which is probably not what you want.

Anyway, here's your db

VehicleFuelLogs.zip

[breakingme10]

WOW! I just learned SOOOOOO much from that! I didn't realize that the captions couldn't use # (unit#)! I honestly don't have all the drivers information. But once i do have all that information for them that is what i planned on doing. Expanding the database as I get the information. I just really need to make sure and be careful to get everything initially set up so that when I do add information like that it doesn't change anything on my existing tables. I don't know if I even got all the truck information at the moment and we had a guy flip one of the tankers this weekend so we are buying a new truck and so i know my truck data will be changing.

[rpeare]

I honestly don't know if a field alias will screw it up or not, I'm just pointing out you're using it. I do not use any reserved words, any special characters, any aliases or any spaces in any of my object names, it has saved me oodles of time over the years.

[breakingme10]

ok i'm sure this is a very dumb question, but in my db i could switch to different tables with tabs at the top of the table window. with yours there must be a setting that is different and there aren't tabs anymore.

[June7]

rpeare set database option for overlapping windows.

File > Options > Current Database > select options as desired

I also prefer overlapping windows. Can size objects to view them side-by-side.

[rpeare]

I hate tabbed windows!

[breakingme10]

Oh i love them. I could have swore I saw an option to import a sql and now i don't anymore. I am truly losing my mind! How do I calculate endDate also? I'm sorry I really am I'm just sooo stressed I can't even think straight

[breakingme10]

So i would use DateAdd (ww, 2?) not sure how to get it to only go from monday to the second sunday (12/30/13-1/12/14)

[rpeare]

you have 2 week pay periods, you just add 13 days to the starting date and you get your end date, just like I did on the form

[breakingme10]

DateAdd ('d', 13, #12/30/2013#) ? I've tried DateAdd ('d',13,[StartDate]) but it doesn't work i've also tried DateAdd('d',13,#[StartDate]#)

[breakingme10]

Oh so it doesn't work in the table like that