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

[ConnorGiles]

Ive reuploaded the DB with my parameter query to show you better what i mean - make sure to include parameters to make sure it works.

 

[ConnorGiles]

ahhh - so what is your end goal?

what are you searching for?

 

[torz]

so basically, a query that can be run @ any point during the month that will return all of the calls are in the 7 day period that are more than 1 call for the same custId

 

[ConnorGiles]

So if you have a customer that has phoned twice in the last 7 days - he should show up - but if a customer has phoned once it shouldnt?

 

[torz]

exactly

and if possible I wouldn't want to see the first call that came through - because that would be perfectly acceptable, just anything after that


Now that you say it like that - explained so simple I feel retarded

 

[ConnorGiles]

Basically an Find a duplicate query may be the answer -

This is what is used to find duplicates of fields

But this would have to mean everything needs to be identicle in the CustID column

Then it would show you the amount of times duplicated

ive attached the database with the duplicates query to show you

 

[ConnorGiles]

ive added an extra field with a duplicated customer ID and that was the result - if thats what you need - there you go.

 

[ConnorGiles]

you would need to suite the query to your needs ofcourse but that should help you out a little

Hope all is okay

 

[ConnorGiles]

Now that you say it like that - explained so simple I feel retarded

No reason to

I'm terrible at explaining in text because i speak with my hands

Don't ever feel like that because there is no reason to

we're all friends here

 

[torz]

Kind of the idea that I'm after...

If you have a look at the OLD query now, it would be perfect if it did not show the record with customer ID 20


** this is obviously showing the first call which is fine, I can handle that because it seems a little more difficult than I first thought to get exactly what I was looking for **

 

[ConnorGiles]

so you want the agent to be an identifier aswell?

 

[torz]

pretty much everything that is in that query....

So that the manager of this team jumps in runs the query and can see the custID that is coming back into the team, the agents that had assisted & their notes & date of the call.

So if the table had another entry for custID 20 within the last 7 days it should show them as it does now, but because there is only 1 entry I don't want it to show the record.

If the table has multiple customers with multiple calls within the last 7 days, I'd want to see everything as in the last sample DB

 

[ConnorGiles]

I'm afraid to say this issue must be resumed tomorrow if any other people dont come to assist you :/ sorry for the delay in advance

So basically you want it so that it identifies the customers who have called twice not once - so the duplicate query will need to be launched - This will make sure that every customer who has phoned twice to the same agent and so forth (THE INFO IN EACH FIELD NEEDS TO BE THE SAME TO A TEE this also includes capitals)

this will then show a duplicate value and anyone that has phoned once will not show

*See Find Duplicate Values Query* thats the logistics

i will be back with you tomorrow bright and early at 8:30

Cya then!

 

[torz]

OMG :banghead::banghead::banghead::banghead::banghead::banghead:

This was SOOOOOOOOOOOOOOO easy after you mentioned that last bit and I googled it and realized that access 2010 basically does it all for you!

Its basically/is what VBAiNET said ages ago that I couldn't get to work properly with his example and my stupidness!!!

SELECT Table1.CustID, Table1.InterDate, Table1.Agent, Table1.Note
FROM Table1
WHERE (((Table1.CustID) In (SELECT [CustID] FROM [Table1] As Tmp GROUP BY [CustID] HAVING Count(*)>1 )) AND ((Table1.InterDate) Between Date() And Date()-7))
ORDER BY Table1.CustID;

attached the DB with it working in case anyone else is as retarded as I am

Thank you so much for all the help! Sorry it took so long for me to get across what I was trying to get out if the DB :banghead:

So happy now

 

[torz]

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

You were basically spot on mate (I'm sure you already knew that )

 

[vbaInet]

I wanted to see how you two got on, didn't want to interrupt

 

[torz]

I wanted to see how you two got on, didn't want to interrupt

LOL

Hope we provided your day/nights entertainment

Just had a look at what you posted / my stupid attempt and the final result (SQL) can see what I was missing, all a learning curve...

Did you ever get a chance to look at that SYStray one I uploaded for you?
I think in the end I'm going to take your advise and mod the ribbon & hide the navigation table thingo but leave the actual window there, you seem to know what your talking about

 

[vbaInet]

LOL

Hope we provided your day/nights entertainment

I had some pop-corn too Only kidding!

Did you ever get a chance to look at that SYStray one I uploaded for you?
I think in the end I'm going to take your advise and mod the ribbon & hide the navigation table thingo but leave the actual window there, you seem to know what your talking about

I've got the tab open but not just had the time to look at it yet. The thing is I don't want any of my applications/browsers running when I'm testing such a thing just in case it hangs. So the challenge is having the time to shutdown.

The main problem there is that you shouldn't really be using the systray as a way of getting back to your application. Your users will get confused. The taskbar is there for showing visible running applications, whereas the systray is for hidden applications (whether running or not) or notifications.
I'll still have a look anyway, in case you want it for other purposes.

 

[ConnorGiles]

As promised im back Glad you got it sorted

Yes Vba is correct as usual

Such a know it all! i'm kidding

 

[torz]

mmmm I think I got a little ahead of myself... it doesn't quite work, so close but yet so far