Query almost done

[trythis]

I was given this code that worked great but I need to tweak it.

TRANSFORM
First(Format([StartDateTime],"HH:MM")) AS ValFld
SELECT YourTable.Name
FROM YourTable
GROUP BY YourTable.Name
PIVOT Format(Int([StartDateTime]),"dd-mm-yy");

row is name column is date and value is starttime I need to add the type of shift to the start time D for day shift E evening shift N Night shift S sick V vacation

so the cross junctions of the crosstab query would show 7:00Am D

I thought maybe I could merge the fields?

the shiftstart and the shifttype fields

Thanks,
Tina

 

[namliam]

In query design you can just add more columns to the "row" of the crosstab...

Instead of just pasting code try and research a little and understand what the person(s) on this forum are giving you. If you dont understand it, ask beyond what is given you in code...

Just the code is 10% of the job !

 

[trythis]

In query design you can just add more columns to the "row" of the crosstab...

Instead of just pasting code try and research a little and understand what the person(s) on this forum are giving you. If you dont understand it, ask beyond what is given you in code...

Just the code is 10% of the job !

I didn't just past code you big jerk. I have studied and worked on this and broke it twice trying different things. I have added columns and rows and merges and nothing has given me what I want.

If you are going to be rude you can go play some where else. I realize it is frustrating to deal with new people who are learning, but don't be rude.

Tina

 

[Kryst51]

I didn't just past code you big jerk. I have studied and worked on this and broke it twice trying different things. I have added columns and rows and merges and nothing has given me what I want.

If you are going to be rude you can go play some where else. I realize it is frustrating to deal with new people who are learning, but don't be rude.

Tina

I don't think he was trying to be a jerk, your OP implies that is what you did, copy and paste,

I was given this code that worked great but I need to tweak it.

--Emphasis (bold) mine

He is just giving advice based on his understanding of what you're doing.

 

[namliam]

I didn't just past code you big jerk. I have studied and worked on this and broke it twice trying different things. I have added columns and rows and merges and nothing has given me what I want.

If you are going to be rude you can go play some where else. I realize it is frustrating to deal with new people who are learning, but don't be rude.

I am sorry if I offended you by no means and appearently Kryst agree's atleast partially...

I did provide help, which you happily choose to ignore. AND I might add I help many new people online or at work EACH day without fail, why would I go and be treating you any different ??

I see many people who 'claim' to understand advice I give them, turn around and act differently... Which is why I try and stimulate people to understand not just use... and why I made the comment.
Again Appoligies if I offended you, even if I didnt mean to offend you.

If you understand a pivot table, then you know there is an extra line in query design. You can only have 1 field set as column, but you can have as many fields set as Row as you like. As I understand your question you want to add an additional field to the row aspect of your pivot.
Thus my advice... (repeat from above)

In query design you can just add more columns to the "row" of the crosstab...

What part of this is not clear?
What part of this is not working?
What part of this doesnt cover your question?

 

[trythis]

I am sorry. My reply was uncalled for. I do not understand pivot tables, but I'm trying and obviously getting frustrated in the process.

Apparently I don't communicate very well either since you all thought I wasn't trying.

I will study some more and see what I can find.

Again I apologize to you all for my negative attitude.
Thanks,
Tina

 

[Kryst51]

Hey, Try namliam's suggestion about going to the design view of the query, if that doesn't work then post back here.

 

[namliam]

I do not understand pivot tables, but I'm trying and obviously getting frustrated in the process.
...
I wasn't trying.

We all know the feeling of frustration when your not getting anywhere .... and I am sure you are trying !!!

Keep up the good work

 

[trythis]

I have added the ShiftType as a row like suggested.
I have attached two views showing what I have.

I just need it to show me what type of shift each time is.

It shows me the start times but each start time could be start of vacation, sick, funeral, or training.

How do I make it show me shift type for every date and every time???

I tried to add ShiftType as a value and just get an error message.

Thanks,
Tina

(could anyone suggest some training classes? I have researched several online courses.)

 

[trythis]

OK,

I have discovered this

TRANSFORM First(Format([StartDateTime],"HH:MM") & " " & [shifttype]) AS ValFld
SELECT YourTable.Name
FROM YourTable
GROUP BY YourTable.Name
PIVOT Format(Int([StartDateTime]),"dd/mm/yy");

after I tweaked it, it worked perfectly.

I need some training.

This looks very simple and I do not know how to do it.

Suggested training classes would be greatly appreciated.

Thanks,
Tina

 

[namliam]

I would have guessed your previous was what you are looking for

But if what you have now works for you, perfect

I am sorry but could not point you to a course to save my life! 100% self tought!