Duplicate Field Query Question

[Collins]

I have a two table query. Table one's records are intersections. County Road 42 and Elm. Table two is the crashes at that intersection for the last 10 years. So... 2010 - 2 crashes. 2011 - 1 crash. 2012 - 0 crashes. 2013 - 2 crashes and so on. However the query only needs to show the crashes for a range of 3 years. So say 2011, 2012 and 2013. (which by the way I would like the user to be able to determine) In my table I have a field named CrashYear and a field named NumberofCrashes. When I bring my intersection information into the query everything works but when I bring CrashYear down and NumberofCrashes down into my query I get the same intersection repeated 10 times (one for each year). When I bring down a second CrashYear and NumberofCrashes the query no longer works and I get zero data.

How can I get a query that lists the intersection once. and then 3 different crash years (determined by the user) with the corresponding number of crashes for that year?

[JamesDeckert]

You could easily get this by producing a report based on the query so that you get something like
42nd and elm
2005 3
2006 8
2007 1

For us to try to figure out your query problem, it would be easier if you posted the SQL
I don't understand why you're bringing down a field more than once.

[Collins]

For starters I'm a beginner at this. I took an online intermediate class on Access so I know the basics. SQL is WAY beyond me at this point. So to post an SQL statement would be impossible. I'm trying to use the Query builder in Access to do this. The way I would like the query to look would be as follows. Across the top.

County Road - Intersection - 2005 - 2006 - 2007
42 - Elm - 3 - 8 - 1

Is that even possible?

[JamesDeckert]

yes, but challenging. Will you always want 3 years? That may make it easier.
you don't need to know SQL to paste your code here.
In query builder, click down arrow under view and select SQL view, then copy and paste into your post.

[Collins]

Is this what you're looking for?

SELECT [TblIntersections Broken Out].Hwy, [TblIntersections Broken Out].Intersection, [TblIntersections Broken Out].CityTWP, [TblIntersections Broken Out].RefPnt, [TblIntersections Broken Out].CityCode, TblCrashes.CrashYear, TblCrashes.NumberofCrashes, TblCrashes.CrashYear, TblCrashes.NumberofCrashes, TblCrashes.CrashYear, TblCrashes.NumberofCrashes, [TblIntersections Broken Out].NewLocal, [TblIntersections Broken Out].TrafficControl
FROM [TblIntersections Broken Out] INNER JOIN TblCrashes ON [TblIntersections Broken Out].IntersectionID = TblCrashes.IntersectionID;

[JamesDeckert]

is CrashYear a string or number?

[JamesDeckert]

You can do this with 3 subqueries and a parent query (or 2 and 1) or it can be done by calling a function which returns the number of crashes for all 3 years in a string.

Here is the basis for the query method - I took out other fields, you can put them back
parent query
SELECT [TblIntersections Broken Out].Intersection, Query1.[3 years ago], Query2.[2 years ago], Query3.[Last Year]
FROM (([TblIntersections Broken Out] LEFT JOIN Query1 ON [TblIntersections Broken Out].IntersectionID = Query1.IntersectionID) LEFT JOIN Query2 ON [TblIntersections Broken Out].IntersectionID = Query2.IntersectionID) LEFT JOIN Query3 ON [TblIntersections Broken Out].IntersectionID = Query3.IntersectionID;

subqueries
SELECT [TblIntersections Broken Out].Intersection, TblCrashes.NumberofCrashes AS [3 years ago], [TblIntersections Broken Out].IntersectionID
FROM [TblIntersections Broken Out] INNER JOIN TblCrashes ON [TblIntersections Broken Out].IntersectionID = TblCrashes.IntersectionID
WHERE (((TblCrashes.CrashYear)=Year(Date())-3));

SELECT [TblIntersections Broken Out].Intersection, TblCrashes.NumberofCrashes AS [2 years ago], [TblIntersections Broken Out].IntersectionID
FROM [TblIntersections Broken Out] INNER JOIN TblCrashes ON [TblIntersections Broken Out].IntersectionID = TblCrashes.IntersectionID
WHERE (((TblCrashes.CrashYear)=Year(Date())-2));

SELECT [TblIntersections Broken Out].Intersection, TblCrashes.NumberofCrashes AS [Last Year], [TblIntersections Broken Out].IntersectionID
FROM [TblIntersections Broken Out] INNER JOIN TblCrashes ON [TblIntersections Broken Out].IntersectionID = TblCrashes.IntersectionID
WHERE (((TblCrashes.CrashYear)=Year(Date())-1));




You'll notice that I hard coded the years to be the previous 3 years. So two challenges I haven't figured out, how to prompt for a year (easily done in a single query, but If done in three queries the user will be prompted 3 times for the 3 years) and the second issue is the field names which you'll notice I named, "3 years ago", etc. I haven't found a way to programmatically name a query field.

to try out these queries, just copy the SQL into an empty query where you copied your sql from then you can go into design mode when you want to add more fields.

[Collins]

WOW!!! That worked slick. Thanks... That is very close to what I need. But I gotta have the years in the column headers and make them selectable... Any thoughts on that or are you stumped?

[JamesDeckert]

changing the field names could be accomplished by building the queries in VBA as well as prompting for the initial year to base the subqueries on.
I don't know and couldn't find any documentation on changing field names based on input, within the query itself.

[June7]

Field names in a SELECT query object design cannot be dynamic.

Getting the years to output horizontally would be done with CROSSTAB query. It is not easy to build stable CROSSTAB query to be the RecordSource for a report because the fields can be dynamic. Review http://allenbrowne.com/ser-67.html

It is possible to emulate a CROSSTAB with expressions. http://datapigtechnologies.com/flashfiles/crosstab.html

SELECT IntersectionID, Max(IIf([CrashYear]=Forms!formname!tbxYear, [NumberOfCrashes], 0)) AS Yr1, Max(IIf([CrashYear]=Forms!formname!tbxYear + 1, [NumberOfCrashes], 0)) AS Yr2, Max(IIf([CrashYear]=Forms!formname!tbxYear + 2, [NumberOfCrashes], 0)) AS Yr3 FROM tableORquery WHERE CrashYear BETWEEN Forms!formname!tbxYear AND Forms!formname!tbxYear + 2 GROUP BY IntersectionID;

[JamesDeckert]

June has your answer for being able to pick a year. Put the year in a form, then reference the form from the query. You still won't be able to name the columns by the year however unless you code the sql in VBA and build the sql string. So how important is it to have the column headers with the actual year versus Yr1, Yr2, Yr3?