Microsoft Access Query Questions

pts.
Tags:
Access
I have created a MS Access database and am experiencing two issues: 1) I need to create a query that displays results of specific records that have been created (this criteria is based on a date column) during three separate timeframes: a) The previous seven days. b) The previous month. c) The previous six months. d) The previous year. For example, if the query is run on January 27, 2006, it will display records that were created from the previous seven days, month, six months, and year of that date. If it is run on January 28, 2006, the results will be different than the query ran on the 27th because the date the query ran has changed. I hope that I am making sense. :) I think that I will need to create four separate queries to get the results and I will also need to prompt the user for the date (i.e., 01/27/06 or 01/28/06, etc so that the query will return the correct data). Please advise if four separate queries are needed. Also, what formula or criteria would I use for each query? I have tried several different methods and the results are not correct. 2) I loathe the reporting tool in MS Access. I am not an advanced user and have been learning a lot. The reports are difficult to create and are so ugly! (The reports will be eventually distributed to the CIO and executive management and need to look highly professional). Is there an easier way to run reports out of Access or is there another reporting tool I could use after extracting the data from the query(s)? I thought about creating macros in Excel for this, however since the data frequently changes, the macro will not work each time. I am trying to automate the function of querying and reporting as much as possible. Thank you in advance for your assistance. Catherine

Answer Wiki

Thanks. We'll let you know when a new response is added.

Catherine,

You can use a single query to get all of you data, since the group of records created in the previous year is a superset of all of the smaller groups:

SELECT [Field1]
FROM table1
WHERE DateDiff(“y”,[Field1],Date())<= 365
ORDER BY [field1] DESC;

Note: You will need to be careful to change the 365 to 366 on leap years (or if you can get the modulus function to work then you can use a conditional if (iif) in the where clause).

As for the Access report writer: I don’t really like it either. You can get a third party report writer like Crystal Reports. The only thing there is, Access uses an older version of Crystal Reports, so the interface is very similar. If you still with the Access report writer, try using the wizard to create you reports. For this particular report I think you will probably need to have three subreports to accomodate your subsets of data.

I hope this helps.

CJC

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SKMEHAB
    Hi, The solution provided by is good enough to create the report but you would need to separate it in different groups. MS Access is good if you want less to program. The problem with ms access query/report is that it does not follow consistent date format rather highly depends on System date format setting, so be carefull. Second, MS Access reports are not so difficult rather it is quite handy and easy to do. I would suggest you get some good book on ms access. I have been using it for developing applications where I do not have plenty of time. Good Luck
    0 pointsBadges:
    report
  • Alewis
    Catherine, To get all of the data to return in a single recordset, which I guess is what you mean by a 'single query', you could use a UNION statement. Use four individual select statements, employing the where clause that Charles JC recommended, one for each of the date ranges required, connected by UNION ALL. You can use a field that returns a different constant in each of the selects to indicate which type of date range is represented by the row for break processing and etc., if that is important. If you want to get fancier, you could use the UNION query as the data source for an additional query to perform extended aggregate processing, just be aware of potential performance problems with this approach. Regarding MS Access as a report writer, the tool is excellent and provides advanced features that are available with a little programming experience. Skmehab's recommendation is well advised. If you are going to be doing advanced report writing, Crystal probably has as much of a learning curve as Access. Hope this helps. Best, Alvin
    0 pointsBadges:
    report
  • Ronn
    Catherine, To answer you first question, I believe that your best bet is to use 4 separate queries to obtain your results; however, all 4 will be using the DateAdd function. Here are the SQL statements I came up with that should return the results you require: a) The previous seven days. SELECT "Previous 7 Days" AS Timeframe, LogDate FROM Log WHERE LogDate>DateAdd("d",-7,Date()) ORDER BY Log.LogDate; b) The previous month. SELECT "Previous Month" AS Timeframe, LogDate FROM Log WHERE LogDate > DateAdd("m",-1,Date()) ORDER BY Log.LogDate; c) The previous 6 months. SELECT "Previous 6 Months" AS Timeframe, LogDate FROM Log WHERE LogDate > DateAdd("m",-6,Date()) ORDER BY Log.LogDate; d) The previous year. SELECT "Previous Year" AS Timeframe, LogDate FROM Log WHERE LogDate > DateAdd("yyyy",-1,Date()) ORDER BY Log.LogDate; If you want to go further, save each of the above queries as TestA, TestB, TestC, and TestD to correspond to your a, b, c, and d. Then the following UNION Query would present them all together: SELECT * FROM TestA UNION SELECT * FROM TestB UNION SELECT * FROM TestC UNION SELECT * FROM TestD; Now from this Union Query you can use the Report Wizard to create a fairly decent report: 1 - Select the Union Query for the Report Wizard to work with. 2 - Select all the fields to include in your report. 3 - Select the Timeframe field for grouping. 4 - Select LogDate for sorting ascending. From there you can decide on the layout and style. Once you have named it, you can go into Design mode and fix it up any way you wish. Now for your second question regarding the reporting tool in MS Access. It is a fairly "plain" reporting tool and not very sophisticated; but, it does a fairly good job, especially for the novice. It can turn out some nice reports too, if you know all the tricks, like the one above. One other trick you may need to use for this situation is the "sub-report" ability if you don't want to use the Union Query. For more sophisticated reports, I have used Crystal Reports with very good luck. Again, it can be used by the novice user; but, it has some very nice features for the more experienced user. I hope this helps. Good luck!
    0 pointsBadges:
    report
  • DaveInAZ
    I just want to add that, having worked with multiple report generators over the years including Access, Crystal, and Excel, Access is by far the easiest to produce an advanced, professional report in. As someone already suggested, I would recommend using the Wizard to create the basis of your report, and then modify it as needed. You can change fonts and colors, add graphics, change the background... whatever you want to do to make it "pretty". It does require learning to use the tool, though, as would any other report generator that offers any flexibility in design.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following