Dates, Recordsets, SQL HELP!!

[Burty]

Hi

Hope someone can help me with this, i have been using access for a a short time but have come up against something that i'm a bit stuck on.
I'm not sure if i'm going about it the right way.

What i want to do is create a query based on 1 table, the table records problems logged by a customer and what time and date they were logged. What i want to be able to do is query that table and find out
1. if there is more than one problem reported by customer.
2. if there is more than one problem check if there was a problem reported withing the last 7 days.
3. display in the query all the records that meet the criteria of 1 + 2
4. All this needs to be run from a form which specifies the date in which the calls were logged. i.e logged from nov 1st to 15th nov

I created a duplicates query by customerID that shows all records where a customer has called more than once.

i thought i could use the query as the source of 2 recordsets and use rst1 to check if job date falls into the dates specified by the form dates then loop through the records 1 by 1 in rst2 to see if there are any records that are within 7 days of the last record.

Thats the theory, however i haven't a clue how i would put that into practice as i've never really used recordsets before, or am i going about this in completely the wrong way??

Any help would be really appreciated

 

[ColinEssex]

if there is more than one problem reported by customer.

does this mean a customer can report more than one problem or is it one problem has been reported by several customers?

Col

 

[Burty]

Hi Col

Thanks for coming back so quickly in response to your question, The customer can have more than one problem, rather than a number customers with the same problem.

 

[Burty]

Is there anyone who can help me Please???

 

[KabirPatel]

I'm more used to SQL Server than Access so you will need to change things regarding dates:

If you had a table as follows:

CustomerID ProblemDate
--------------------------------
Cus001 21/01/2007
Cus001 22/01/2007
Cus002 21/01/2007

then you would write the following query to get a listing of all customers that have more than 1 problem.

SELECT CustomerID
FROM CustomerTable
GROUP BY CustomerID
HAVING COUNT(*) > 1

You could do the following to get a listing of all customers that have more than 1 problem over the last 7 days (but will need to change the Dateadd and getdate() bits as these are SQL Server functions - I believe that now() is used in Access).

SELECT CustomerID
FROM CustomerTable
WHERE ProblemDate > DATEADD("dd", -7, getdate())
GROUP BY CustomerID
HAVING COUNT(*) > 1

Number 3 in your original question doesn't make sense.

Cheers,
Kabir

 

[Burty]

Thanks Kabir

that sql works, however it doesn't give me what i'm after.
what i need to be able to do is see records where the problem date is within 7 days of the last problem date recorded against that customer id.
Not records in the last 7 days. Does that make sense??

Cheers

Burty