Solved

MS Access Query

Posted on 2014-12-29
15
112 Views
Last Modified: 2014-12-31
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.
0
Comment
Question by:Sbovino
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +1
15 Comments
 
LVL 36

Expert Comment

by:PatHartman
ID: 40522736
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
 
LVL 30

Expert Comment

by:hnasr
ID: 40522795
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
 

Author Comment

by:Sbovino
ID: 40522925
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40522959
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
 

Author Comment

by:Sbovino
ID: 40523888
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40523979
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 40524277
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
 

Author Comment

by:Sbovino
ID: 40524460
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 40524580
+/-: (['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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40524653
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
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
ID: 40524670
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
 

Author Closing Comment

by:Sbovino
ID: 40524805
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40524821
Glad I could help ... Have a Happy and Safe New Year!

ET
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40525658
@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
 

Author Comment

by:Sbovino
ID: 40526292
Thanks for the advice. Well said.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question