Solved

Access07 - Reliability of a CrossTab Query results in a Form's design.

Posted on 2014-01-26
3
325 Views
Last Modified: 2014-01-27
Hello All

For a particular Form I need the results of a CrossTab query.  That Ctab query will have ItemIDs as its columns and Day's total info as its rows.

Since I've never used CTab queries I wanted to know how reliable they will be in my Form or will it "blow" up if an Item all of a sudden has no info for a day when before it always had info....

EXAMPLE - My Crosstab query is based on this query's code.
SELECT qry_InfoDetails_EndofDay.BusDay, qry_InfoDetails_EndofDay.MachPollPositionID, qry_InfoDetails_EndofDay.AmtIn, qry_InfoDetails_EndofDay.EndofDay
FROM qry_InfoDetailsLastPullDate INNER JOIN qry_InfoDetails_EndofDay ON qry_InfoDetailsLastPullDate.MachPollPositionID = qry_InfoDetails_EndofDay.MachPollPositionID
WHERE (((qry_InfoDetails_EndofDay.BusDay)>=[qry_InfoDetailsLastPullDate].[MaxBusDay]))
GROUP BY qry_InfoDetails_EndofDay.BusDay, qry_InfoDetails_EndofDay.MachPollPositionID, qry_InfoDetails_EndofDay.AmtIn, qry_InfoDetails_EndofDay.EndofDay
ORDER BY qry_InfoDetails_EndofDay.BusDay, qry_InfoDetails_EndofDay.MachPollPositionID;

Open in new window


THEN...My cross tab query will pull info as:
TRANSFORM Sum(qry_LVLInfoDetailEODSinceLastPull.AmtIn) AS SumOfAmtIn
SELECT qry_LVLInfoDetailEODSinceLastPull.BusDay, Sum(qry_LVLInfoDetailEODSinceLastPull.AmtIn) AS [Total Of AmtIn]
FROM qry_LVLInfoDetailEODSinceLastPull
GROUP BY qry_LVLInfoDetailEODSinceLastPull.BusDay
PIVOT qry_LVLInfoDetailEODSinceLastPull.LVLMachPollPositionID;

Open in new window


Since my MachPollPositionID's always will be numbers 1 to 10 but if there is NO EndofDay = True records will it bomb out or is there a way in the first query I can have it put a zero (0) number in every poll position for data=today that would prevent a null query.
0
Comment
Question by:wlwebb
3 Comments
 
LVL 5

Accepted Solution

by:
Lawrence Barnes earned 500 total points
ID: 39810092
Cross tabs are great, but where there's missing data and nothing to create an expected column, the forms do crash.

You can use the NZ function to put in a zero if the value is null on the first query to handle cases of no data.

SELECT qry_InfoDetails_EndofDay.BusDay, qry_InfoDetails_EndofDay.MachPollPositionID, NZ(qry_InfoDetails_EndofDay.AmtIn,0) as AmtIn, qry_InfoDetails_EndofDay.EndofDay

Also, for the columns going on the cross tab results I always wanted the column names (fields displayed in the forms) to have the same names.  So instead of the column name being a date, I changed it to Day-1, Day-2, Day-3 so that the form fields would be stack and the user new that Day-10 was 10 days ago.
0
 

Author Closing Comment

by:wlwebb
ID: 39811949
Thanks for the suggestion.
0
 
LVL 10

Expert Comment

by:Luke Chung
ID: 39812002
Use the IN clause to require column values to avoid problems with missing data.

Here's an example of using a report to display a 12 month report for any date range: http://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/index.html

The same can be done for a form.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HasData 9 36
Running Access application from Task Scheduler 6 33
Sending email from List Data 2 51
Qry for calculation on form, on click of Checkbox 3 5
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

910 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

25 Experts available now in Live!

Get 1:1 Help Now