Query on dates

[Mauricianstyle]

Hi,

Thank you for the access file.

I don't understand why do we have "3" on the "overlapcount" column for the "User20" ?

Many thanks for your help.

 

[vbaInet]

User 23, 22 and 20.

 

[Mauricianstyle]

Ok thank you.

For the final result, how can I have the peak connection for each day within the last thirty days ?

 

[vbaInet]

Give me some examples like before.

 

[Mauricianstyle]

I would like that result.

See attached

Thank you.

 

[vbaInet]

Can you include the before data and the after in spreadsheet format.

 

[Mauricianstyle]

Hi,

Here is the spread sheet format attached.

The User23, logged in the 12/05/2015 and logged off the 13/05/2015.
Can we split it into 2 different dates to have a MaxCount by date ?

Thank you

 

[vbaInet]

The User23, logged in the 12/05/2015 and logged off the 13/05/2015.
Can we split it into 2 different dates to have a MaxCount by date ?

What have you tried?
And what if it spans more than two days, e.g. from 12/05/2015 to 15/05/2015, how do you then split it?

 

[Mauricianstyle]

Hi,

I import the data into a pivot table in Excel and i had the result i sent you in my last post.
For the date 12/05/2015 we have a peak of 7 but we should have 4 (User20, User21, User22, User23).
I would like the peak only for one day and not from 12/05/2015 to 13/05/2015.

Thank you

 

[vbaInet]

You misunderstand my question.

What do you mean by this?

The User23, logged in the 12/05/2015 and logged off the 13/05/2015.
Can we split it into 2 different dates to have a MaxCount by date ?

 

[Mauricianstyle]

I need to have the peak connection for each day from 00:00AM to 11h59PM.

When a user logged in the 12/05/15 and logged out the 13/05/15.
We must have a Maxcount for the 12/05/15 from 00:00AM to 11h59PM.
And for the 13/05/15 From 00:00AM to 11h59PM.

 

[vbaInet]

Ok, I follow now.

So with this,

User23	12/05/2015 16:43	13/05/2015 11:48

you will need to fabricate records for User 23 so that it now becomes:

User23	12/05/2015 16:43	12/05/2015 23:59
User23	13/05/2015 00:00	13/05/2015 11:48

 

[Mauricianstyle]

Hi,

Thank you for your answer. I have created a new record for the user23 and it is working very well.
In that example i have few records so I modified it manually.

In my table I have about 60 000 records
To feed my databasse I have created a macro that import an Excel file.

 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "LOG", "D:\TEST\LOG.xlsx", 0

So I need to fabricate automatically a new record when I find a "logoff date > logon date"
Do I have to do this before the import in the Excel file or is it possible to do it during the import ?

 

[vbaInet]

What is in the Excel file

You fabricate the records using the table where the data exists.

 

[Mauricianstyle]

Here is two excel files.

1. LOG : First Tab the Initial log from the software and the second Tab the initial log + the new record for the user23.
2. Tables : These are the lists that i will import into my Access Database. (Here I put it in the same file but when imported it will be in three different files.

So i will have three tables LogOn, LogOff and User.

 

[vbaInet]

If the original data is a date/time value why split it into two parts, only to bring it back together in the query?

Whatever you do, the answer is to fabricate the records before using it in your charts.

 

[rykid16]

Hello guys I was wondering how do I pull, one and only one date. I want to pull the most recent date. I have only been able to figure out how to pull the most recent not the single date part.

 

[vbaInet]

Welcome to AWF rykid6!

Considering that you're new you probably don't know how things work around here. What happens is if a question asked by another poster isn't entirely related to the original post, you would create a new thread describing your problem. Have a go at doing that but in the meantime, to answer your question, you can look into using the Max() function in a totals query. Or consider using the DMax() function in the Control Source of your textbox or in VBA.

 

[Mauricianstyle]

Hi,

Thank you very much for all your answers. That really helped me.
I will make a Macro to change my records.