Creating a query in MS Access

The UK's most regular instructor-led training courses.
Training information: access class microsoft training · Access training · Microsoft Access Training London
See also · microsoft access training · access vba courses · access database course

Resolved · Low Priority · Version 2003

2 posts

ReplyThu 4 Feb 2010, 13:27Delegate Tim said...

Tim has attended:
Access Introduction course

Creating a query in MS Access

Hi, having had the Introduction to MS Access 2003 course on 2 Feb, I have now received the post-course reference manual (all 105 pages of it!) but there is no mention of Queries in it, and this is one of our principal requirements.
We have three databases of names (which don't need a relationship), from which we need to extract queries of who was in the UK during any given two-month period. Anthony Malone said that the way we were doing this was inefficient, not to mention wrong, and that we could set up a query that would ask for start and end dates for the period required each time it was clicked on before producing a table of results. I have created a query, taking fields from one of the three databases in question, but can't make the above happen. I seem to remember something about putting <[Enter] and >[Enter] in the two date columns but this isn't working.

Version	Date	Location	Places available	Book	Next place rate (£)
Access Advanced 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice
2003	Fri 19 Mar 2010	Bloomsbury	4		£235	£240
2007	Wed 24 Mar 2010	Bloomsbury	5		£240	£245
2003	Tue 6 Apr 2010	Southwark	9		£203	£208
2007	Thu 13 May 2010	Bloomsbury	7		£188	£193
2003	Tue 18 May 2010	Bloomsbury	9		£182	£187
2003	Fri 2 Jul 2010	Bloomsbury	7		£85	£90
Full Schedule: See all 11 Access Advanced course dates. Bookings currently available until 14th December 2010.

336 posts

ReplyThu 4 Feb 2010, 13:59Trainer Anthony said...

RE: Creating a query in MS Access

Hi Tim, thanks for your query and sorry to hear about the lack of information about queries, we will address that.

In your queries, your date requirements were "hard wired' in. In the training session we replaced those hard wired dates in the criteria boxes with:

Between [Enter Start Date] And [Enter End Date]

This will trigger two input boxes with the text in square brackets on each one and the dates entered will be fed back into the query. In this way, you can enter your date requirements far more easily that editing the query itself each time

I hope this helps. Let me know how you get on.

Anthony

2 posts

ReplyThu 4 Feb 2010, 14:34Delegate Tim said...

RE: Creating a query in MS Access

Hi Anthony,

Done! I had to add a < and a > before the square brackets in order to capture the two-month period, so I was on the right lines - it was just a matter of knowing what to put in the brackets. Anyway, thanks for your help (and it won't be the last time, I guarantee!) - it works a treat!
Can you check-out the reference manual please and forward any stuff about queries that ends up going in.

Cheers,

Tim

753 posts

ReplyThu 4 Feb 2010, 14:00Trainer Stephen said...

RE: Creating a query in MS Access

hi Tim

Thanks for your question

The type of query you are referring to is called a parameter query. In the example give, you would use the following syntax

Between [Enter First date] and [Enter second date]

Hope this helps

Regards

Stephen

· It's so easy to Access the information you need
· MS Access Top Tips: Takeaways From Microsoft Access Advanced Training
· User-Proofing Your Access Database: Part 1
· Take an Access Course to Learn About Relational Databases
· Practice Microsoft Access Database Decorum To Reap The Rewards

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

Access tip:

Copy a Previous Record's Values to a New Record

If you often enter the same value in one field of a table, there are two methods to save re-typing the data.

1. Use Ctrl+' (apostrophe) to repeat the value input in the previous record.

2. Change the field's DefaultValue property in Design View to the most commonly used value.

View all Access hints and tips