fixing a table to conform to current system

D

DawnTreader

Hello All

i have a table that i used to store each login and log out of each user on a
seperate record. so it would look like this:

LoginLogId DateLoggedIn EmployeeId Type SystemModule
32886 2009/01/27 7:47:59 AM 228 Out Server
32885 2009/01/27 7:45:50 AM 228 Out Server
32884 2009/01/27 7:45:39 AM 228 In Server
32883 2009/01/27 7:38:29 AM 323 In Server
32882 2009/01/27 7:29:08 AM 311 Out Client DB
32881 2009/01/27 6:10:06 AM 275 Out Client DB
32880 2009/01/27 6:07:08 AM 311 In Client DB
32879 2009/01/27 6:00:02 AM 315 Out Client DB
32878 2009/01/27 5:58:25 AM 315 In Client DB
32877 2009/01/27 5:27:22 AM 275 In Client DB
32876 2009/01/27 4:18:03 AM 334 Out Client DB
32875 2009/01/27 3:50:48 AM 345 Out Client DB
32874 2009/01/27 3:32:31 AM 334 In Client DB
32873 2009/01/27 3:30:15 AM 334 Out Client DB

now i want to rearrange that and make it happen like this:

LoginId EmpId Type SystemModule DateIn DateOut
32667 228 In and Out Server 27/01/2009 01:53:32 PM 27/01/2009 01:53:37 PM
32666 228 In and Out Server 27/01/2009 11:21:50 AM 27/01/2009 11:22:08 AM
32665 228 In Server 27/01/2009 11:18:43 AM
32664 228 In and Out Server 27/01/2009 11:15:33 AM 27/01/2009 11:15:37 AM
32663 228 In Server 27/01/2009 11:15:01 AM
32662 228 In and Out Server 27/01/2009 11:14:15 AM 27/01/2009 11:14:24 AM
32661 228 In Server 27/01/2009 11:11:47 AM 27/01/2009 11:12:05 AM

sorry for the crowdedness, but i think you get the idea. the question is how
can i take the old data and convert it to the way the new data is layed out?
basically i need to take a stack of 2 records and put it on one line as one
record. all the previous data is in pairs, kinda.

the problem is if someone crashed out of the database there may only be an
"In".

the reason for the change is to allow me to use one line per login and out
and to calculate the time logged in easier than trying to find the 2 records
that matched.

as always, any help or ideas are greatly welcomed!
 
K

KARL DEWEY

You can not have each LoginLogId showing and roll the data up. This crosstab
query should do what you need.

TRANSFORM Min(DawnTreader.DateLoggedIn) AS MinOfDateLoggedIn
SELECT DawnTreader.EmployeeId, DawnTreader.SystemModule
FROM DawnTreader
GROUP BY DawnTreader.EmployeeId, DawnTreader.SystemModule,
Format([DateLoggedIn],"yyyymmdd")
PIVOT IIf([Type]="In","Date Logged In","Date Logged Out");
 
D

DawnTreader

Hello Karl

Fantastic! this is exactly what i needed. kinda!

after creating the crosstab i created an append query to put the data into
the new table i created to do the logins to.

Fantastic! thanks for the insight. this might help with some other things
that i have been working on! :)

KARL DEWEY said:
You can not have each LoginLogId showing and roll the data up. This crosstab
query should do what you need.

TRANSFORM Min(DawnTreader.DateLoggedIn) AS MinOfDateLoggedIn
SELECT DawnTreader.EmployeeId, DawnTreader.SystemModule
FROM DawnTreader
GROUP BY DawnTreader.EmployeeId, DawnTreader.SystemModule,
Format([DateLoggedIn],"yyyymmdd")
PIVOT IIf([Type]="In","Date Logged In","Date Logged Out");

--
KARL DEWEY
Build a little - Test a little


DawnTreader said:
Hello All

i have a table that i used to store each login and log out of each user on a
seperate record. so it would look like this:

LoginLogId DateLoggedIn EmployeeId Type SystemModule
32886 2009/01/27 7:47:59 AM 228 Out Server
32885 2009/01/27 7:45:50 AM 228 Out Server
32884 2009/01/27 7:45:39 AM 228 In Server
32883 2009/01/27 7:38:29 AM 323 In Server
32882 2009/01/27 7:29:08 AM 311 Out Client DB
32881 2009/01/27 6:10:06 AM 275 Out Client DB
32880 2009/01/27 6:07:08 AM 311 In Client DB
32879 2009/01/27 6:00:02 AM 315 Out Client DB
32878 2009/01/27 5:58:25 AM 315 In Client DB
32877 2009/01/27 5:27:22 AM 275 In Client DB
32876 2009/01/27 4:18:03 AM 334 Out Client DB
32875 2009/01/27 3:50:48 AM 345 Out Client DB
32874 2009/01/27 3:32:31 AM 334 In Client DB
32873 2009/01/27 3:30:15 AM 334 Out Client DB

now i want to rearrange that and make it happen like this:

LoginId EmpId Type SystemModule DateIn DateOut
32667 228 In and Out Server 27/01/2009 01:53:32 PM 27/01/2009 01:53:37 PM
32666 228 In and Out Server 27/01/2009 11:21:50 AM 27/01/2009 11:22:08 AM
32665 228 In Server 27/01/2009 11:18:43 AM
32664 228 In and Out Server 27/01/2009 11:15:33 AM 27/01/2009 11:15:37 AM
32663 228 In Server 27/01/2009 11:15:01 AM
32662 228 In and Out Server 27/01/2009 11:14:15 AM 27/01/2009 11:14:24 AM
32661 228 In Server 27/01/2009 11:11:47 AM 27/01/2009 11:12:05 AM

sorry for the crowdedness, but i think you get the idea. the question is how
can i take the old data and convert it to the way the new data is layed out?
basically i need to take a stack of 2 records and put it on one line as one
record. all the previous data is in pairs, kinda.

the problem is if someone crashed out of the database there may only be an
"In".

the reason for the change is to allow me to use one line per login and out
and to calculate the time logged in easier than trying to find the 2 records
that matched.

as always, any help or ideas are greatly welcomed!
Click to expand...
Click to expand...
 
K

KARL DEWEY

It does not need the Format([DateLoggedIn],"yyyymmdd")

TRANSFORM Min(DawnTreader.DateLoggedIn) AS MinOfDateLoggedIn
SELECT DawnTreader.EmployeeId, DawnTreader.SystemModule
FROM DawnTreader
GROUP BY DawnTreader.EmployeeId, DawnTreader.SystemModule
PIVOT IIf([Type]="In","Date Logged In","Date Logged Out");

--
KARL DEWEY
Build a little - Test a little


DawnTreader said:
Hello Karl

Fantastic! this is exactly what i needed. kinda!

after creating the crosstab i created an append query to put the data into
the new table i created to do the logins to.

Fantastic! thanks for the insight. this might help with some other things
that i have been working on! :)

KARL DEWEY said:
You can not have each LoginLogId showing and roll the data up. This crosstab
query should do what you need.

TRANSFORM Min(DawnTreader.DateLoggedIn) AS MinOfDateLoggedIn
SELECT DawnTreader.EmployeeId, DawnTreader.SystemModule
FROM DawnTreader
GROUP BY DawnTreader.EmployeeId, DawnTreader.SystemModule,
Format([DateLoggedIn],"yyyymmdd")
PIVOT IIf([Type]="In","Date Logged In","Date Logged Out");

--
KARL DEWEY
Build a little - Test a little


DawnTreader said:
Hello All

i have a table that i used to store each login and log out of each user on a
seperate record. so it would look like this:

LoginLogId DateLoggedIn EmployeeId Type SystemModule
32886 2009/01/27 7:47:59 AM 228 Out Server
32885 2009/01/27 7:45:50 AM 228 Out Server
32884 2009/01/27 7:45:39 AM 228 In Server
32883 2009/01/27 7:38:29 AM 323 In Server
32882 2009/01/27 7:29:08 AM 311 Out Client DB
32881 2009/01/27 6:10:06 AM 275 Out Client DB
32880 2009/01/27 6:07:08 AM 311 In Client DB
32879 2009/01/27 6:00:02 AM 315 Out Client DB
32878 2009/01/27 5:58:25 AM 315 In Client DB
32877 2009/01/27 5:27:22 AM 275 In Client DB
32876 2009/01/27 4:18:03 AM 334 Out Client DB
32875 2009/01/27 3:50:48 AM 345 Out Client DB
32874 2009/01/27 3:32:31 AM 334 In Client DB
32873 2009/01/27 3:30:15 AM 334 Out Client DB

now i want to rearrange that and make it happen like this:

LoginId EmpId Type SystemModule DateIn DateOut
32667 228 In and Out Server 27/01/2009 01:53:32 PM 27/01/2009 01:53:37 PM
32666 228 In and Out Server 27/01/2009 11:21:50 AM 27/01/2009 11:22:08 AM
32665 228 In Server 27/01/2009 11:18:43 AM
32664 228 In and Out Server 27/01/2009 11:15:33 AM 27/01/2009 11:15:37 AM
32663 228 In Server 27/01/2009 11:15:01 AM
32662 228 In and Out Server 27/01/2009 11:14:15 AM 27/01/2009 11:14:24 AM
32661 228 In Server 27/01/2009 11:11:47 AM 27/01/2009 11:12:05 AM

sorry for the crowdedness, but i think you get the idea. the question is how
can i take the old data and convert it to the way the new data is layed out?
basically i need to take a stack of 2 records and put it on one line as one
record. all the previous data is in pairs, kinda.

the problem is if someone crashed out of the database there may only be an
"In".

the reason for the change is to allow me to use one line per login and out
and to calculate the time logged in easier than trying to find the 2 records
that matched.

as always, any help or ideas are greatly welcomed!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Query based on Ordinal Values 6
Find then highlight in yellow 6
SUMIF or COUNTIF 1
Need to pull <=14 Days, <=30 Days, >30 Days from a date column 3
Lookup value and return column offset value 5
Grouping - Distinct 5
Macro for Copying pasting between different spreadsheets 4
Fiscal year 2

Top