Working with a Query

[MJD1]

As I'm still learning Access, I need a bit of help with the following.
I have a table named "Main table1" that keeps audit scores by 'store', 'date' and 'quarter', and 'last quarter'.
The quarter field contains the following "1st quarter, 2nd Quarter, 3rd quarter and 4th Quarter"
I need to build a report that will show the last (most current scores for the 2nd quarter) and the previous scores that were obtained on the 1st quarter.

I need to build a query that will show me the previous scores (in this case 1st quarter). The problem that i'm faced with is that the query that I built using "Last" shows me the most recent audits scores. What I need is to query the previous audits to those ones.

I tried the Dlookup in the criteria field (to look up the 'Last quarter' field in main table1 but that didnt work. I've tried many other things as well but nothing worked.

Hopefully i've made my question clear enough.

If anyone can guide me on this one I would very much appreciate it. I've been on this for a couple of days now...

cheers Martin.

 

[cghoga]

try using the Between Date in your query.
this way you could specify the exact dates you need.

in the query criteria under the date field enter this:

Between [Enter Begin Date] AND [Enter End Date]

this will prompt you to enter a begin and end date and only pull the values for that date range.

you also mentioned that you only need to pull 1st Quarter scores.

to only pull 1st Quarter scores (you wouldn't use the Between Date above for this, that's a different example) insert something like this in the criteria section of the quarter filed in your query:

"1st Quarter"

or if you're having to do this throughout the year maybe you would want a prompt so you wouldn't be limited to just 1st Quarter scores. Like this:

[Enter Quarter]

this will prompt you to enter the querter you need results for.


sorry if i'm misunderstanding what you need.

hope this helps you.

 

[MJD1]

Thanks for your help. I'm entering the quarter manually right but would like the query to be automatic. In other words when I select a report for a specific store and quarter, the previous quarter will appear on the report wihout being prompted. I tried requesting with the date however I found that using the quarter was better for me.