MS Access 2003 Query Problem

[tpreece01]

I have the following database structure:

Type: Name [Field Name]

Table: Dates [From Date] [To Date]
Table: Errors [User ID] [Error Code} [Error Date]
Table: Error Codes [Error Code] [ Error Value]

The user selects the From Date and To Date

I want a query to list all the user id's with errors between the specified dates. I then want the query to return a total points value (sum) for each user.

I've got a query:

[User ID] [Error Date] [Error Value]

- I can list all errors between the selected dates without any problem.
- I can use the 'sum' to total teh error values, if no dates are selected (ie for the entire database)

What I need is for 'sum' to work in conjunction with the selected dates. It ain't working though!

Can anybody help?

[vascop]

tpreece01
I have done what you want. However, with a basic query you cannot display the dates, but you can do it for only those which come between the dates you type in.

I dont know why you have a table called dates as this is not necessary and how does the name field fit in? If this is the name of the user then it should be in the errors table. You only need the two tables errors and error codes.
How can I tell you how to do this?
Do you want me to type in the details of the query?
I can type it in as SQL. Do you know about SQL?
Or would you prefer a description which uses the basic MS Access way of defining a query?

[vascop]

i have posted a word document with a screen capture of the query in my Google documents. You can view it here:
http://docs.google.com/Doc?id=dfzbst2k_789gbkt fb
You may need to zoom it to read it.
Let me know how you get on.

[vascop]

Right click on the image in the link and download it. You can then view it more easily.
Let me know if you have any problems.
Vasco

[tpreece01]

Vascop, thanks a lot for your answers.

Re the user id - there is another table [users]. User id is the primary key and the table includes names, teams...

I'm actually expanding an old database that used a table called dates. It contains only one record and two fields (from and to). The dates are overwritten on each change.

If there is a better way of doing it, which I'm sure there is, then I'll definitely look at it.

I have touched upon SQL many years ago, but don't have a great knowledge... I'd like to get to grips with it again though. However, I have just been using the basic Access query builder upto now.

Thanks for your help.

So anything you can offer will be really appreciated.

[tpreece01]

Vascop, I've tried thq query the way you've done it in your link and all is working, thankyou.

To complicate matters, as well as the tables I originally listed, here are two others:

Table: Users [User ID] [User Name] [Team Code]
Table: Teams [Team Code] [Team Leader] [Manager]

So, while using {Where: Between [from] And [to]} that you provided, can I now also filter this by Team Code?

I've added Teams[Team Code] Where [Teams]![Team Code] to the query, but it doesn't seem to work. The query runs without asking for a team code...

[tpreece01]

Here I go again!

Scrub that last post...
I simply needed a colon at the end of the Where statement to prompt for a Team Code.. Also [Team Code] had a relationship with [User ID] and with [Team Leader] which confused it a bit!

[tpreece01]

vascop, using:

WHERE ((([Error Log].[Date Logged]) Between [Error logged from: (dd/mm/yyyy)] And [Error logged to: (dd/mm/yyyy)]) AND ((Teams.[Team Code])=[Team Code:]))

I'm outputting the query onto a report. Can I display the user entered date paramaters on the report also?