Solved

MS Access Query

Posted on 2014-12-29
15
106 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
  • 5
  • 5
  • 4
  • +1
15 Comments
 
LVL 34

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
 
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 34

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 34

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 34

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now