Use a query to show data from two different months

[cptdave1996]

Hello everyone!
Here is the layout of my table.



Autonumber Ware, David

Ware, David
What I would like to do is make a query and report so that I can have this data to should all the fields for the row with 5/31/2015 as the end date and only the enddate, miles driven, percentageOfGoal, and days worked fields for the row 4/30/2015.
Does this make sense to anyone of what I am trying to do? My boss would like to be able to compare the last two months of data so that he can see if the person improved from the previous month.
Not sure if this can be done with the way I have the table set up.
Help?
If I need to send a copy of the table I can!

[June7]

I am blocked from viewing linked images. Providing db never hurts. Probably more than 1 way to accomplish what you want. Here is one possibility http://allenbrowne.com/subquery-01.html#AnotherRecord

[cptdave1996]

Ok, I have uploaded a copy of the DB. Thanks!
I am a novice so bear with me if I ask a lot of questions.

[June7]

Not seeing db attachment.

[cptdave1996]

Uploaded again!

[June7]

The LogViol table is not a normalized structure. If each record can be only one violation type, then should be one field with choices. I have seen situations where filtering on multiple Yes/No fields along with other fields caused lot of frustration, even impossible. (Same for yes/no fields in DriverLoadCount).

Should not save employee name parts in related tables, only EmployeeID. Advise not setting lookups in table (I prefer to see actual value when I view tables, not alias). Same advice for MobEmployeeList.

EmployeeID in EmployeeInfo should be primary key.

DriverID field in DriverLoadCount has a Lookup on a field DriverID# in MobEmployeeList - there is no field with # in name.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Really need to fix structural issues before proceeding.

Meanwhile, consider:
SELECT *, (SELECT TOP 1 Dupe.MilesDriven FROM [Driver Performance] AS Dupe WHERE Dupe.[Driver Name]=[Driver Performance].[Driver Name] AND Dupe.EndDate<[Driver Performance].EndDate ORDER BY Dupe.EndDate DESC, Dupe.PerfID) AS PriorMiles FROM [Driver Performance];

[cptdave1996]

thanks but I am not sure if I follow you about the LogViol table or about the employee name parts in related tables? So you would have to type the actual value each time you enter it in the field, i.e. active or inactive and MOB or GRN? Is this what you are referring too?
I deleted some old tables because we are not going to use them any more.
Naming convention?
I have uploaded an updated zip.

[June7]

Naming convention: the schema you use to name things - whether to use spaces or not, UPPERCASE, under_score, ProperCase, etc.

LogViol table has text fields for driver LName and FName but not a field for driver ID.

I was referring to the multiple yes/no fields for violation types. It appears that each record can have only 1 type (30Min, Fuel, Ship_Comm, FandM, DVIR) although a few appear to have both FandM and DVIR. I am suggesting instead 1 field and a combobox on a form for selecting value.

Did you see the query I included in previous post? Might have edited after you read post.

[cptdave1996]

Naming convention: the schema you use to name things - whether to use spaces or not, UPPERCASE, under_score, ProperCase, etc.

LogViol table has text fields for driver LName and FName but not a field for driver ID.

I was referring to the multiple yes/no fields for violation types. It appears that each record can have only 1 type (30Min, Fuel, Ship_Comm, FandM, DVIR) although a few appear to have both FandM and DVIR. I am suggesting instead 1 field and a combobox on a form for selecting value.

Did you see the query I included in previous post? Might have edited after you read post.

Ok, thanks. I try to use the same naming convention: i.e. UpperCase and LowerCase without spaces.

I have gotten rid of the log violation table but each record could have more than one violation per day so that is why I had the check boxes.

I did see the query and it looks like that is what I was looking for but how can I set it up so that it only shows the last two months?

Thanks!

[June7]

Apply filter criteria to the outer query.

[cptdave1996]

Apply filter criteria to the outer query.

Ok, thanks for all your help. I will try to get the DB straightened up.
One more thing though, I noticed that the query was written in SQL, is there a way that I can use the query wizard to create the query so that I can see how that is done? I don't know SQL hardly at all.
Thanks again!

[June7]

Copy/paste my query into SQL View of query wizard. Then switch to Design View.

Can do the reverse to learn SQL. Also visit http://www.w3schools.com/sql/default.asp

[cptdave1996]

The query works but I can't get it where it only shows the last two months. Maybe I am trying to get it to do something that it can't or I just don't know what I am doing.
Thanks for all your help. I will just do it in excel and type the data in...

[June7]

Oh, you meant in Design View only shows one field. Sorry. Try this:

SELECT [Driver Performance].*, (SELECT TOP 1 Dupe.MilesDriven FROM [Driver Performance] AS Dupe WHERE Dupe.[Driver Name]=[Driver Performance].[Driver Name] AND Dupe.EndDate<[Driver Performance].EndDate ORDER BY Dupe.EndDate DESC, Dupe.PerfID) AS PriorMiles
FROM [Driver Performance]
WHERE (((Format([EndDate],"yyyymm"))>=Format(DateAdd("m",-1,Date()),"yyyymm")));

[cptdave1996]

Yeah that works. I will just change the 1 to a 2 so that it will skip this month and pick up last month and the month before.
I will close this saying it is solved but I have another question.
can I ask you a question in an email before I post it.