Manipulate Query Data into a table

[bowserj]

I know this is probably very simple, but I would like to read through the records in a query, and write this data to a table after manipulating it. Basically what I am trying to do is get data that is in the form:

ClientName EmployeeName Date HoursWorked


I want to read through each record, and using code, determine the day of the week (ie Sun, Mon, etc) that each date occurrs on and then write that to a table setup as follows:

Sun_EmployeeName Sun_Hours Mon_EmployeeName Mon_Hours...

I plan to maintain a count for each day so I know which record to update. I am trying to create a schedule that shows the days of the week and what employees are working each day and when they are working.

Any help would be awesome....

Thanks

 

[JBinQLD]

G'day Mate. You're already storing your data so why store it again? Just present it differently. Create a query, view it as SQL and paste this in:

TRANSFORM Sum([RED]tblEmployees[/RED].HoursWorked) AS SumOfHoursWorked
SELECT [RED]tblEmployees[/RED].EmployeeName
FROM [RED]tblEmployees[/RED]
GROUP BY [Red]tblEmployees[/RED].EmployeeName
PIVOT Format([date],"ddd");

I've assumed the table is called tblEmployees - correct this to your actual table name.

 

[bowserj]

I have looked at something similar to this, but I need to present the data accross a page, and don't want to have one record on each line, I want to manipulate the data and drop it into individual fields in the new table so I can have data that looks like this:

SUN MON TUES WED THURS FRI SAT
DATA DATA DATA DATA DATA DATA
DATA DATA DATA
DATA DATA

Where I believe doing as you suggested would give me:

SUN MON TUES WED THURS FRI SAT
DATA
DATA
DATA
DATA
DATA
DATA
DATA
DATA
DATA
DATA
DATA

for the same data.

Thanks for the suggestion though. I may end up using that method if I cannot find a way to do what I need to do....

 

[fneily]

see:
How to concatenate multiple child records into a single value faq701-4233

 

[JBinQLD]

No mate, by incorporating the Sum function it shouldn't do that, it should give the result you require. You may need to fiddle but you can achieve your results