Need help making query (1 Viewer)
- Thread starter kirkm
- Start date
isladogs
MVP / VIP
- Local time
- Today, 10:05
- Joined
- Jan 14, 2017
- Messages
- 18,209
Make a query and add 2 copies of your table to it.
The second copy will be shown as TableName_1
Join the tables by the Title field.
Add the title and year field from the first copy of the table.
In the year field criteria, enter [TableName_1].[YearField] +1
That's it.
The second copy will be shown as TableName_1
Join the tables by the Title field.
Add the title and year field from the first copy of the table.
In the year field criteria, enter [TableName_1].[YearField] +1
That's it.
Colin, I'm not getting any output from it. That may be as my Year field is text ?
In Query design Access is adding quotes around the "1".
Also my Join Property has 1 selected. That's ok? I tried using Val(Year) and putting brackets around the 1, but no joy.
In Query design Access is adding quotes around the "1".
Also my Join Property has 1 selected. That's ok? I tried using Val(Year) and putting brackets around the 1, but no joy.
isladogs
MVP / VIP
- Local time
- Today, 10:05
- Joined
- Jan 14, 2017
- Messages
- 18,209
This should work .... though I'm not sure why you would use a text field for the Year
For info, Year is a RESERVED word in Access and so shouldn't be used for field names.
Code:
SELECT tblMain4.Title, CInt([tblMain4].[Year]) AS Expr1
FROM tblMain4 INNER JOIN tblMain4 AS tblMain4_1 ON tblMain4.Title = tblMain4_1.Title
WHERE (((CInt([tblMain4].[Year]))=CInt([tblMain4_1].[Year])+1));For info, Year is a RESERVED word in Access and so shouldn't be used for field names.
