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

ConnorGiles

Strange Traveller
Local time
Today, 04:44
Joined
Aug 12, 2014
Messages
1,068
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.
 

Attachments

  • Sample DB.accdb
    688 KB · Views: 57

ConnorGiles

Strange Traveller
Local time
Today, 04:44
Joined
Aug 12, 2014
Messages
1,068
ahhh - so what is your end goal?

what are you searching for?
 

torz

Registered User.
Local time
Today, 13:44
Joined
Jun 21, 2014
Messages
73
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

Strange Traveller
Local time
Today, 04:44
Joined
Aug 12, 2014
Messages
1,068
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

Registered User.
Local time
Today, 13:44
Joined
Jun 21, 2014
Messages
73
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 :D
 
Last edited:

ConnorGiles

Strange Traveller
Local time
Today, 04:44
Joined
Aug 12, 2014
Messages
1,068
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 :)
 

Attachments

  • Sample DB.accdb
    772 KB · Views: 63

ConnorGiles

Strange Traveller
Local time
Today, 04:44
Joined
Aug 12, 2014
Messages
1,068
ive added an extra field with a duplicated customer ID and that was the result - if thats what you need - there you go.
 

ConnorGiles

Strange Traveller
Local time
Today, 04:44
Joined
Aug 12, 2014
Messages
1,068
you would need to suite the query to your needs ofcourse but that should help you out a little :)

Hope all is okay
 

ConnorGiles

Strange Traveller
Local time
Today, 04:44
Joined
Aug 12, 2014
Messages
1,068
Now that you say it like that - explained so simple I feel retarded :D

No reason to :)

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

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

we're all friends here :p
 

torz

Registered User.
Local time
Today, 13:44
Joined
Jun 21, 2014
Messages
73
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 :) **
 

Attachments

  • Sample DB (1).accdb
    772 KB · Views: 60
Last edited:

ConnorGiles

Strange Traveller
Local time
Today, 04:44
Joined
Aug 12, 2014
Messages
1,068
so you want the agent to be an identifier aswell?
 

torz

Registered User.
Local time
Today, 13:44
Joined
Jun 21, 2014
Messages
73
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

Strange Traveller
Local time
Today, 04:44
Joined
Aug 12, 2014
Messages
1,068
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

Registered User.
Local time
Today, 13:44
Joined
Jun 21, 2014
Messages
73
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!!! :eek:

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 :D
 

Attachments

  • Sample DB (1).accdb
    864 KB · Views: 68

torz

Registered User.
Local time
Today, 13:44
Joined
Jun 21, 2014
Messages
73
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 :D)
 

vbaInet

AWF VIP
Local time
Today, 04:44
Joined
Jan 22, 2010
Messages
26,374
I wanted to see how you two got on, didn't want to interrupt ;)
 

torz

Registered User.
Local time
Today, 13:44
Joined
Jun 21, 2014
Messages
73
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 ;) :D
 

vbaInet

AWF VIP
Local time
Today, 04:44
Joined
Jan 22, 2010
Messages
26,374
LOL

Hope we provided your day/nights entertainment ;)
I had some pop-corn too :D 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 ;) :D
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

Strange Traveller
Local time
Today, 04:44
Joined
Aug 12, 2014
Messages
1,068
As promised im back :) Glad you got it sorted

Yes Vba is correct as usual ;)

Such a know it all! ;) i'm kidding
 

torz

Registered User.
Local time
Today, 13:44
Joined
Jun 21, 2014
Messages
73
mmmm I think I got a little ahead of myself... it doesn't quite work, so close but yet so far :p
 

Users who are viewing this thread

Top Bottom