Assistance with Query - Show with count > 1 between 2 dates (1 Viewer)

[torz]

hey guys,

Hope someone is able to give me a hand with this query, I get close to what I need but not quite there....

Basically what I'm trying to do is get a query to return results when the count of a certain field's value is greater than 1 & it is between 2 dates (the current date and 7 days prior to the current date).

Ideally I'd only want to show the results for anything after the first counted record between those dates, but if that's not possible that's totally fine I can work with that ...

I've attached a sample DB which hopefully give you a better idea what I'm trying to do, because I'm generally horrible at trying to explain it via text :banghead:


Thanks heaps all!!

 

[ConnorGiles]

Currently Playing opening the sample - my answer will be with you shortly

 

[torz]

Thank you!!

 

[ConnorGiles]

Recreate the query - that seems to be the problem within your sample database - somewhere must be an error which is resulting in showing no info - recreate the query and test that for me please.

Thanks

Connor

 

[ConnorGiles]

Ofcourse use all of the same values as before " Between Date() and Date()-7 " and ">1" in my recreated query the search results come out okay

Ive reuploaded the database but with my new query which is the exact same as your other query. but you may have accidently tempered with the property sheets or something

a quick new query should salvage an answer for you.

 

[vbaInet]

I've not looked at your db but it sounds like this:

WHERE [Field] BETWEEN Date() AND Date()-7
GROUP BY ... etc
HAVING Count([Field]) > 1

 

[ConnorGiles]

And may i ask what you mean by "show the results for anything after the first counted record between those dates" - clarify on this and ill help you through it

 

[ConnorGiles]

In your older query you do have totals enabled - get rid of that - should work fine.

 

[torz]

And may i ask what you mean by "show the results for anything after the first counted record between those dates" - clarify on this and ill help you through it

Sure....

So the DB itself if basically going to be used in a call centre to track calls coming through to a help desk...

The first call that comes through is totally okay, any calls that come after that within the last 7 days are classed as a repeat call that should have been avoided.

 

[ConnorGiles]

Sure....

So the DB itself if basically going to be used in a call centre to track calls coming through to a help desk...

The first call that comes through is totally okay, any calls that come after that within the last 7 days are classed as a repeat call that should have been avoided.

So basically what your saying is that you wish for no repeats from the same caller?

 

[torz]

Ofcourse use all of the same values as before " Between Date() and Date()-7 " and ">1" in my recreated query the search results come out okay

Ive reuploaded the database but with my new query which is the exact same as your other query. but you may have accidently tempered with the property sheets or something

a quick new query should salvage an answer for you.

This query is pretty close but it should not be showing the 3rd record because the customer ID is not >1

 

[torz]

lol I wish you could IM on forums, so we don't want more calls (no matter who it comes from) for the same CUSTID.

does that make sense? I hope so I'm hopeless at trying o explain stuff via text

 

[ConnorGiles]

All of the records are greater than 1 torz? that is if your talking about custID

 

[torz]

All of the records are greater than 1 torz? that is if your talking about custID

So there is 4 entries in the table 3 with 2000000000 & 1 with 20

basically what I'd like to see is:

Record 1 & 2 because they are the same CUSTID within 7 days. record 4 shouldn't show because its greater than 7 days

Record 3 shouldn't show because its not the same CUSTID

So it should only show records within the 7 day time frame that have a count of 2 or more

 

[ConnorGiles]

Record 4 doesnt show as requested

Record 3 was only changed to 20 by me to test

To be able to search for a specific CustID so only one shows would require you to have the criteria "2000000000" or "20" for example.

so if you wish to only show one CustID at a time rather create a parameter value which would ask you for a CustID and it would show every call from that customer's ID or in your query search for a specific CustID such as "20"

 

[torz]

okay I think I get what your saying... I tried what VBAiNET mentioned

SELECT Table1.Note, Table1.Agent, Table1.InterDate, Table1.CustID
FROM Table1
WHERE InterDate BETWEEN Date() AND Date()-7
GROUP BY Table1.Note, Table1.Agent, Table1.InterDate, Table1.CustID
HAVING Count([CustID]) > 1;

but get the same result as my original...

If you use the criteria like you mentions, that means I will have to do a query for every custid in the db?? is that correct?

if that is the case then it would take way to long (millions of customers)

 

[torz]

did you have teamviewer? http://www.teamviewer.com/en/download/windows.aspx

and I can show you the actual live db

 

[ConnorGiles]

Or use a paramater query as i said before - which will prompt you for a specific CustID everytime you start the query

 

[ConnorGiles]

Im sorry torz but im at work this will just have to do :/

 

[torz]

haha no worries

the parameter query would be impossible as well, there is like 40 odd people in the desk that take roughly 500+ calls a day, from a base of roughly 10+ million customers

so in the 7 day period you may have for example 1000 different customers, but 2+ calls for the same customer...