Qry and report - format possibilities

[aucho22]

Hello guys,

I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?

I have a query QryMainReport:

Start Date/Time
End Date/Time
Employee

At the moment this is what the format of my report looks like (I removed other unnecessary fields):

StartTime----------EndTime---------------Employee
12/06/2014 01:00 --12/06/2014 03:00------John Smith
12/06/2014 04:00 --12/06/2014 06:00------Jane Doe
13/06/2014 02:00 --13/06/2014 05:00------John Smith
13/06/2014 08:00 --13/06/2014 08:00------Jane Doe

I attached a screenshot of what I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that.

I suppose first it would mean:

• I would have to do a query to separate the times from the dates?
  
• I would have to find a way for Access to find the “unique dates” and “unique names”?
  
• Does it mean I have to use cross tab queries?

I hope it makes sense

Thank you as usual for all your guidance,
Aucho22

 

[plog]

Yes, this is a cross-tab query. Additionally, making reports off cross-tab queries is extremely difficult, so if you want to somehow format the final output my advice is to use Excel for that. You could build your cross-tab and export the data, or you could build an aggregate query, export the data then pivot it in Excel to achieve the layout you want.

A cross-tab is a specialized Aggregate query, which means finding "unique" values isn't something you will have to go out of your way to do. The cross-tab will naturally do that. My advice is to build a regular query first, bringing in all the fields you will need (you are going to have to extract out the Date and the Time values from the StartTime/EndTime fields). Once you have those showing correctly, I'd save that query, copy it, then convert it into a cross-tab.

Give it a shot and if you have difficulty, post back here any issues you are having.

 

[aucho22]

Hello Plog,

I do apologize for the late answer Thank you for your reply,

I will try your advice and let you know if I have any problems!

Fingers crossed

Auho22