Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-28-2009, 06:21 AM   #1
KenHigg
Registered User
 
Join Date: Jun 2004
Posts: 13,309
Thanks: 8
Thanked 155 Times in 129 Posts
KenHigg has a spectacular aura about KenHigg has a spectacular aura about
Gaps in data

I am building a report that gives the user the option of going back a certain number of days that they would enter. Say they want to see all of data back to 1/1/2009. Or maybe go back to 4/1/2008. So what it would do is count the number of records for each month. Say Jan 08 may be 150, Feb 08 something like 125, etc. This is pretty simple using a totals query. Unless there are some months with no records. Using a totals query you end up with the entire month missing in the result. And this causes problem in the chart where I would like to see a '0' for that month.

So.... Is there a trick out there that I may not be aware of? The only way I can think of to do this is to create a temp table, populate it with period data, etc... ugh

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

ken

“You don’t learn to walk by following rules. You learn by doing, and by falling over.”
KenHigg is offline   Reply With Quote
Old 05-28-2009, 06:28 AM   #2
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: Gaps in data

Could you not create a cross tab query from your summary and propogate the column headings using the In() statement. A common question asked here.

My only reservation with this approach is if the user selects a large date range. Maybe you could enforce a limit on the number of periods they could select.

I have a working solution going off line now but wil pick it up tomorrow.

David
__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Old 05-28-2009, 06:31 AM   #3
KenHigg
Registered User
 
Join Date: Jun 2004
Posts: 13,309
Thanks: 8
Thanked 155 Times in 129 Posts
KenHigg has a spectacular aura about KenHigg has a spectacular aura about
Re: Gaps in data

Hum.... I'm not sure I follow. If you have a working example I'd love to see it. Just remember I need to hardwire it to a chart...

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

ken

“You don’t learn to walk by following rules. You learn by doing, and by falling over.”
KenHigg is offline   Reply With Quote
Old 05-28-2009, 06:32 AM   #4
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Gaps in data

Yes, dummy table... Only way to go

You should try making a table filled with values: 00... 31
From this using a couple of queries you can make very date from 01-01-2000 to 31-12-2031
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 05-29-2009, 12:15 AM   #5
DCrake
Remembered
 
DCrake's Avatar
 
Join Date: Jun 2005
Location: Burnley, Lancashire
Posts: 8,634
Thanks: 8
Thanked 325 Times in 208 Posts
DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light DCrake is a glorious beacon of light
Send a message via Skype™ to DCrake
Re: Gaps in data

Ken

Here is a stripped down demo of a employee database that the user wanted to analyse periods where there were gaps in the data. If you look at the frmReportMaker form you will see how I populate the combo boxes with all the available periods. This is monthly based but you could change it days, whatever.

It displays the results on a form in the way of a list box, but also has the additional functionality of exporting the data into a formatted Excel file.

To see this you will need to have the template in the same location as the mdb.
Attached Files
File Type: zip EmployeeWizardDemo.zip (102.3 KB, 55 views)

__________________
David Crake


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
The Home of Simple Software Solutions.

O/S Windows XP (SP3) & Windows 7 64bit
Access 2003 (version 11.0)
Access 2007 (version 12.0)

Remember when posting sample databases you will get a better response if it is pre Access 2007 - not all people have it installed.
DCrake is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
"You tried to execute a query that does not include..." Error 3122 snowdove510 Queries 6 06-15-2011 08:19 AM
Beginner's Guide to ODBC Banana Access FAQs 1 06-18-2009 08:10 PM
Getting error in data base after using insert sql command in vba asingh Modules & VBA 5 07-15-2005 07:12 AM
Using FindFast with SQL Table? jonno99 Modules & VBA 2 07-08-2005 01:31 PM
[SOLVED] Need blank form/subform - get runtime error MonicaS Forms 4 10-17-2000 10:04 AM




All times are GMT -8. The time now is 01:57 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World