Access SQL Query to Pivot based on one colum

[venkateshr28]

Hello Experts,

Need your help on desiging my first access query,

I am working on a Daily dashboard tracker for which i have 3 sheets in excel as mentioned below
Sheet1 = Attendance & Break dump
Sheet2 = Training and Meeting dump
Sheet3 = Headcount file with staff intime details day wise
I have imported those sheets in Access as tables and I am trying to get the Headcount sheet updated with Available hours, Break time, Training and Meeting minutes

Attendance and Break dump is little complicated, staff ABCD for date12/1/2018 will have multiple rows with type defined as Available hours, Break time against which start time, duration will be available

so far i have arrived at a SQL query as below and the type is not getting split to colums, there are 2 type (Available Hours, Break Time) this needs to be 2 columns and duration for respecitve to be updated.

SELECT [Bank ID], [Type], [Dates], SUM(Duration)
FROM Attendance_Break_data
GROUP BY [Bank ID], [Type], [Dates]
ORDER BY [Dates], [Bank ID];

Can some one help on this please.
Since i am working on restricted data i am not able to share the same here, please bare with me.

 

[Minty]

Without seeing a sample of your data this is a bit difficult, but I think you need to change your query to a crosstab query, and make the [Type] a column heading.

As an aside I'm pretty sure Type is a reserved word so may be worth changing it so something else before getting too deep into the design.

 

[venkateshr28]

Hi Minty, Very well said, your absolutly right and with your idea i was able to arrive at the below statement, however there is one more need in this for which i need your help please. Along with this i need to get one more column that is "start time" based on condition if type is avalable i need the start time against that to be pulled in to the below result. Would you be able to help me?

TRANSFORM Round(Sum([Duration]),2) AS Data
SELECT A_B.[Bank ID], A_B.[date]
FROM A_B
GROUP BY A_B.[Bank ID], A_B.[date]
PIVOT A_B.[Type];

 

[Minty]

Again without your data it's difficult. Change your query back to a select query and add a calculated field something like;

CalcStartTime: Iif([Type] Is Not Null, [StartTimeField],0)

See if this gives you what you want. If it does then Turn it back to a crosstab.