MS Access Query

I have a query which pulls from a crosstab.  I am trying to determine how to reference one of the cross tab columns by using data supplied from a form/  I tried referencing the form field, i.e ftecrosstab.[forms]![teacherresources]![combocurrentyear] and that returned an error - not recognized as a valid fieldname Here is the SQL

SELECT ftecrosstab.trschool_id, ([2015-2016])-([2014-2015]) AS [+/-], schools.schoollName, schools.Type, ftecrosstab.[2013-2014], ftecrosstab.[2014-2015]
FROM schools INNER JOIN ftecrosstab ON schools.school_id = ftecrosstab.trschool_id;

Open in new window


How do I reference ftecrosstab.[2013-2014] which is a column in a crosstab query by using a reference to a form that pulls the date?  I also need to subtract those two values are part of the current query.  The query above works with me supplying the dates as written.  Thanks.
SbovinoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You can't substitute column named dynamically.  They are part of the structure of the query and contribute to the creation of the query's execution plan.  If you need to change the columns a query pulls, you need to build the query using VBA and not use a saved querydef.
0
hnasrCommented:
Give a simple example of few records, and the required output.
A demo database helps speed the resolution.

More clarification may be required after inspecting your feedback.
You can put the year ranges as column headings if consistent, like 1-2, 2-3, ...
0
SbovinoAuthor Commented:
Pat suggests that this cannot be done unless the query is built by VBA.  If that is the case I would need an example to work from.  Here is a picture of the output of the crosstab query referenced above.Crosstab query.  The SQL that prodices this output is listed below.

TRANSFORM nz(Sum(TeacherResources.TeacherFTE),0) AS SumOfTeacherFTE
SELECT TeacherResources.trschool_id, Sum(TeacherResources.TeacherFTE) AS [Total Of TeacherFTE]
FROM TeacherResources
GROUP BY TeacherResources.trschool_id
PIVOT TeacherResources.tryear;

Open in new window


I have also included the output of the SQL statement shown in the first post.   2014-12-29-19-46-57.png
Essentially the crosstab summarizes data for each year in the system.  The second query compares the current year to the proposed year.  All of my queries reference dates that appear on forms.  As noted above I have not been able to determine how to do this with data from the crosstab.  I manually change the dates each year in the SQL statement in the first post.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Eric ShermanAccountant/DeveloperCommented:
Try this ... Put this VBA code on a command button or function you use to run your query.  Use your exact query name and the combo box name for previous year below.  This will dynamically write and save the SQL of your query that pulls from the crosstab using the fields from your Form object.

Dim qdf as QueryDef
Set qdf = CurrentDb.QueryDefs("YourQueryNameHere")

qdf.SQL = "SELECT ftecrosstab.trschool_id, (['" & Forms![teacherresources]![combocurrentyear]  & "'])-(['" & Forms![teacherresources]![combopreviousyear] & "']) AS [+/-], schools.schoollName, schools.Type, ftecrosstab.[2013-2014], ftecrosstab.[2014-2015]
FROM schools INNER JOIN ftecrosstab ON schools.school_id = ftecrosstab.trschool_id;

qdf.Close

Run your query here ....


ET
0
SbovinoAuthor Commented:
ET:

When I placed the code the last line beginning with FROM is in red.  I was not able to detect the error. Perhaps a quote mark out of place.  Thanks for checking.

qdf.SQL = "SELECT ftecrosstab.trschool_id, (['" & Forms![teacherresources]![Combocurrentyear] & "'])-(['" & Forms![teacherresources]![combopreviousyear] & "']) AS [+/-], schools.schoollName, schools.Type, ftecrosstab.[2013-2014], ftecrosstab.[2014-2015]"
FROM schools INNER JOIN ftecrosstab ON schools.school_id = ftecrosstab.trschool_id;

Open in new window


Thanks,
0
Eric ShermanAccountant/DeveloperCommented:
Try this one ... Also you need to provide the name of your second combo box.  I called it combopreviousyear and that may not be the name you used, ok.

qdf.SQL = "SELECT ftecrosstab.trschool_id, (['" & Forms![teacherresources]![Combocurrentyear] & "'])-(['" & Forms![teacherresources]![combopreviousyear] & "']) AS [+/-], schools.schoollName, schools.Type, ftecrosstab.[2013-2014], ftecrosstab.[2014-2015] " & _
"FROM schools INNER JOIN ftecrosstab ON schools.school_id = ftecrosstab.trschool_id;"

Open in new window



ET
0
PatHartmanCommented:
When building SQL in code, it is difficult to get the punctuation correct.  The best solution is to build the string into a variable, then put a stop in the code.  Print the contents of the variable to the debug window.  If you can't see the error, copy the string from the debug window and paste it into the query builder.  Usually you will get a better error message.
0
SbovinoAuthor Commented:
ET:

I guess I am confused now.  I got the query to run.  HOwever when I try to open it I get an error that is does not see +/-: (['2014-2015'])-(['2015-2016']) as a valid field name or expression.  When I remove that expression from the query the data is correct.  

My confusion is about what data is being pulled.  In the sql below I am assuming the form data is determining what data to pull.  Yet, when I change the dates in the SQL those dates are pulled not the dates in my form.  For example if I change the dates to ftecrosstab.[2011-2012] that is the data being pulled.  What I am trying to do is to avoid having to manually enter dates in this query.  Thanks for any insight.

qdf.SQL = "SELECT ftecrosstab.trschool_id, (['" & Forms![schools1]![TeacherResources1]![Combocurrentyear] & "'])-(['" & Forms![schools1]![TeacherResources1]![Comboproposedyear] & "']) AS [+/-], schools.schoollName, schools.Type, ftecrosstab.[2014-2015], ftecrosstab.[2015-2016] " & _
"FROM schools INNER JOIN ftecrosstab ON schools.school_id = ftecrosstab.trschool_id;"

Open in new window


Steve
0
PatHartmanCommented:
+/-: (['2014-2015'])-(['2015-2016'])   is not a valid field name.  Do what I suggested.  You should see the error when you print the SQL string to the immediate window.  There is one or more missing/misplaced quotes.
0
Eric ShermanAccountant/DeveloperCommented:
Sbovino ...

You changed the SQL to ... Forms![schools1]![TeacherResources1]![Combocurrentyear] !!!

Is TeacherResources1 a sub-form on form schools1???  If it is then your SQL should look like this ...

Forms![schools1]![TeacherResources1].Form![Combocurrentyear]
Forms![schools1]![TeacherResources1].Form![Comboproposedyear]


ET
0
Eric ShermanAccountant/DeveloperCommented:
Try this one ... you don't need the single quote.  This should work.

qdf.SQL = "SELECT ftecrosstab.trschool_id, ([" & Forms![schools1]![TeacherResources1].Form![Combocurrentyear] & "])-([" & Forms![schools1]![TeacherResources1].Form![Comboproposedyear] & "]) AS [+/-], schools.schoollName, schools.Type, ftecrosstab.[2014-2015], ftecrosstab.[2015-2016] " & _
"FROM schools INNER JOIN ftecrosstab ON schools.school_id = ftecrosstab.trschool_id;"

Open in new window



ET
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SbovinoAuthor Commented:
ET:

The last suggestion worked perfectly.  I am a full time educator and part time IT guy so I am not always fully up to speed in when I have to develop.  I appreciate your patience and assistance.  I am always learning.  Thanks and Happy New Year!
0
Eric ShermanAccountant/DeveloperCommented:
Glad I could help ... Have a Happy and Safe New Year!

ET
0
PatHartmanCommented:
@Sbovino,
As a non-technical person you are more comfortable with verbose names that include lots of special characters but that caused much of the problem you were having.  But, if you are going to develop apps, you might want to consider adopting a more techno-friendly approach.  If you stick to only allowed characters, you can avoid the use of the square brackets everywhere.   Allowed characters - A-Z, a-z, 0-9, and the underscore.  Names should start with a letter and be less than 30 characters long.  When you name things, put the differentiator first so that when you write code, intellisense will help you.  If you have to write EndOfMonthBalForJan, you'll have to type a lot of characters before you can distinguish month names but JanEndOfMonthBal will get you useful intellisense sooner and I would use JanBal rather than the longer name since a field named this way implies end of month anyway.  Personally, I like CamelCase but others prefer The_Underscore.

Names like [+/-] are especially bad.  Create a form from that query.  Add an event for that control and see what Access names it.  Since none of the three characters is valid for use in a name, Access substitutes the underscore so you end up with
Private Sub Ctl____BeforeUpdate(Cancel As Integer)

End Sub

Open in new window

as the name of that control's BeforeUpdate event.
0
SbovinoAuthor Commented:
Thanks for the advice. Well said.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.