Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access Training and help » Report - query in access

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Report - query in access

Resolved · Urgent Priority · Version 2003

Reply Mon 21 Jul 2008, 11:33Delegate Lisa said...

Lisa has attended:
Excel VBA Intro Intermediate course

Report - query in access

if you have to produce a report. all of the data is many to one.

One "project" has many "stage dates" - how would you do a report to show what "stage" is due weekly per "project"?

For upcoming training course dates see: Pricing & availability

Reply Mon 21 Jul 2008, 13:00Trainer Stephen said...

RE: report - query in access

Hi Lisa

Thank you for your question

Can you clarify the table structure for me. Do you have a stage date table that lists the stage dates for each project, and is this table then linked to the main projects table.
If this is not the structure, could you describe it for me

Thanks

Stephen

Reply Mon 21 Jul 2008, 13:15Delegate Lisa said...

RE: report - query in access

Hi Stephen,

I have, for Example,
a project table - - one item
a programme table - many dates
Each item has many programme dates for each stage - eg
stage 1 - accpeted date, revised date and achieved date
stage 2-8 as above.

What I would like to do is run a report so that it shows say in Stage 1 all of the projects which are due between X and Y dates. But only to show the projects which are due nothing else.

Would i have to do this per stage as there are 3 dates which will need to be shown? How do I do this in general really.

Many Thanks

Reply Mon 21 Jul 2008, 15:32Trainer Stephen said...

RE: report - query in access

Hi Lisa

Thanks for the clarification

You could try building your query with the three date fields included, and then entering the parameter for each date on a different row. The syntax for the parameter would be :

BETWEEN [Enter First date] AND [Enter second date]

The only problem would be that you would have to enter the start and end date three times when the query runs; once for each date. This could be tedious.

It is possible to solve the problem by writing a VBA procedure which would simply prompt for the start and end date and then would embed the variables into the sql statement. We actually carry out just such an exercise on the Access VBA course. It is however too invloved to be detailed here.

Hope this is useful

Regards

Stephen

Access articles

· How Reorganising Your Data Can Take Your Business to the Next Level
· Microsoft Access as a Business's Database Management System
· Easy Updating With Microsoft Access
· How To Use Calculation Fields In Microsoft Access
· How To Understand Access As A Relational Database

Please browse our web site to find out more about
introduction access training and other Microsoft training courses.

Access tip:

Hide Access Tables

One method of stopping your Access tables appearing in Database window, is to rename the table(s) with the prefix usys.

This converts the table into a system object, which cannot be viewed in the Database window.

You can show system objects in the Database window by the following:

Step 1: On the Tools menu, click Options.
Step 2: Click the View tab.
Under Show, select the System Objects check box.
Click OK.

View all Access hints and tips