sorting in a normalizing union query

[abourg8646 via AccessMonster.com]

I have a normalizing union query and the SQL looks like this:
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,
[Time Wkd], "TimeWkd"
FROM Schedule
UNION ALL
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,
[Hrs Worked], "Hours Worked"
FROM Schedule
UNION ALL
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,
[Contract Hrs], "Contract Hours"
FROM Schedule
UNION ALL
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,
[Hol], "Holiday"
FROM Schedule
UNION ALL
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,
[Vac], "Vacation"
FROM Schedule
UNION ALL
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,
[LWOP], "LWOP"
FROM Schedule
UNION ALL
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,
[Mil], "Mil"
FROM Schedule
UNION ALL
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,
[Brev], "Brevement"
FROM Schedule
UNION ALL
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,
[EKV], "EKV"
FROM Schedule
UNION ALL SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,
[O/T], "O/T"
FROM Schedule;

When I run my report it pulls all the data in a/z format. Is there a way to
sort the data so that it apprear like this D2 D1 DO
TimeWkd 0600 0600 0600
Hours Worked 8 8 8
Holiday 0 0 0
Vacation 0 0 0

 

[Jeff Boyce]

You seem to be working very hard to get around what would be a relatively
simple query/report if your data were well-normalized. For example, imagine
the query you'd need if your data were structured something like:

tblHours
HoursID
EmployeeID
Hours
Category (e.g., Contract Hours or Holiday or ...)

A simple Totals query can figure up hours per employee, by category.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Larry Linson]

My psychic powers are running low just now, so I would be reluctant to guess
what you actually want in your report . . . perhaps "D2", "D1", and "DO"
headings just aren't as meaningful to me as they are to you. You do not
indicate how you want the data shown/ordered/grouped by Employee ID, or if,
indeed, you want to select and only report on a single Employee ID per
Report. If you help us understand what you have and what you want, chances
are good that someone will be able to offer useful suggestions.

Larry Linson
Microsoft Office Access MVP

 

[abourg8646 via AccessMonster.com]

I understand this but my boss wants to see the data and it will total at the
end of each row.
I just need to sort the data under each date.

You seem to be working very hard to get around what would be a relatively
simple query/report if your data were well-normalized. For example, imagine
the query you'd need if your data were structured something like:

tblHours
HoursID
EmployeeID
Hours
Category (e.g., Contract Hours or Holiday or ...)

A simple Totals query can figure up hours per employee, by category.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a normalizing union query and the SQL looks like this:
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,

[quoted text clipped - 43 lines]

Holiday 0 0 0
Vacation 0 0 0

 

[abourg8646 via AccessMonster.com]

D0, D1, D2,,,D30 are all dates. Under the date there are 10 values ranging
from Hour worked,Leave, LWOP, Brev. Contract Hour. Under each Date then I
need each value in a certain order.

My psychic powers are running low just now, so I would be reluctant to guess
what you actually want in your report . . . perhaps "D2", "D1", and "DO"
headings just aren't as meaningful to me as they are to you. You do not
indicate how you want the data shown/ordered/grouped by Employee ID, or if,
indeed, you want to select and only report on a single Employee ID per
Report. If you help us understand what you have and what you want, chances
are good that someone will be able to offer useful suggestions.

Larry Linson
Microsoft Office Access MVP

I have a normalizing union query and the SQL looks like this:
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,

[quoted text clipped - 43 lines]

Holiday 0 0 0
Vacation 0 0 0

 

[Larry Linson]

And, how do Employee IDs enter into this, or are the values you want to see
the total for all employees? What selection do you want to have on dates, a
From and To?

Once you consolidate the data with a Union Query, it would seem you are
going to need to do a CrossTab or a Pivot Table to make it look like a
spreadsheet, which seems to be what your goal is, and that seems to be how
your data is stored, too.

Jeff, by the way, was correct that storing your data in proper normalized
relational form would simplify what you are doing. But, you are doing
essentially that with your UNION Query.

Just as an aside... why is the data stored in this format -- are you / your
users updating the Tables directly in Table view? Once you have the UNION
query working, you know, you can convert it to a MakeTable query to put your
data into normalized form, and have an easier time working with it in the
future.

Larry Linson
Microsoft Office Access MVP

D0, D1, D2,,,D30 are all dates. Under the date there are 10 values ranging
from Hour worked,Leave, LWOP, Brev. Contract Hour. Under each Date then I
need each value in a certain order.

My psychic powers are running low just now, so I would be reluctant to
guess
what you actually want in your report . . . perhaps "D2", "D1", and "DO"
headings just aren't as meaningful to me as they are to you. You do not
indicate how you want the data shown/ordered/grouped by Employee ID, or
if,
indeed, you want to select and only report on a single Employee ID per
Report. If you help us understand what you have and what you want, chances
are good that someone will be able to offer useful suggestions.

Larry Linson
Microsoft Office Access MVP

I have a normalizing union query and the SQL looks like this:
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,

[quoted text clipped - 43 lines]

Holiday 0 0 0
Vacation 0 0 0

 

[Jeff Boyce]

It sounds like it would be easier for you to do what's being required by
using a spreadsheet rather than a relational database.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I understand this but my boss wants to see the data and it will total at
the
end of each row.
I just need to sort the data under each date.

You seem to be working very hard to get around what would be a relatively
simple query/report if your data were well-normalized. For example,
imagine
the query you'd need if your data were structured something like:

tblHours
HoursID
EmployeeID
Hours
Category (e.g., Contract Hours or Holiday or ...)

A simple Totals query can figure up hours per employee, by category.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a normalizing union query and the SQL looks like this:
SELECT [Employee ID] as EmployeeID, [Date wkd] as DateWkd,

[quoted text clipped - 43 lines]

Holiday 0 0 0
Vacation 0 0 0

 

[abourg8646 via AccessMonster.com]

I have this in a spreadsheet and I have links going to all these different
sheets filling in data. The problem with the speadsheet is that if you change
a link in one sheet it changes in all sheets. I have taken advice from quite
a few people and I really appreciate all the help. Right know I have my
report working. What I need know is to pull it in by date. I have two SQL
statements that I'm working with. The 1st is for my schedule and it works.
The 2nd is for my stats and I'm getting a syntax error in parameter clause:
1st SQL
PARAMETERS [[Forms]!frmDate![txtEndDate]] DateTime;
TRANSFORM First(Query1.[Time Wkd]) AS FirstOfTime
SELECT Query1.[Employee Name]
FROM Query1
GROUP BY Query1.[Employee Name]
PIVOT "D" & DateDiff("d",[Date wkd],Forms!frmDate!txtEndDate) In ("D6","D5",
"D4","D3","D2","D1","D0");

2nd SQL
PARAMETERS [[Forms]!frmDate![txtEndDate]] DateTime;
TRANSFORM Max([Stats Query].[MaxOfTime Wkd]) AS [MaxOfMaxOfTime Wkd]
SELECT [Stats Query].EmployeeID, [Stats Query].Expr1003
FROM [Stats Query]
GROUP BY [Stats Query].EmployeeID, [Stats Query].Expr1003, [Stats Query].
Expr1
ORDER BY [Stats Query].EmployeeID, [Stats Query].Expr1
PIVOT "D" & DateDiff("d",[Date wkd],Forms!frmDate!txtEndDate) In (“D30â€,
â€D29â€,â€D28â€,â€D27â€,â€D26â€,â€D25â€,â€D24â€,â€D23â€,â€D22â€,â€D21â€,â€D20â€,â€D19â€,â€D18â€,â€D17â€,
â€D16â€,â€D15â€,â€D14â€,â€D13â€,â€D12â€,â€D11â€,â€D10â€,â€D9â€,â€D8â€,â€D7â€,"D6","D5","D4","D3",
"D2","D1","D0");

I don't see the difference.

It sounds like it would be easier for you to do what's being required by
using a spreadsheet rather than a relational database.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I understand this but my boss wants to see the data and it will total at
the

[quoted text clipped - 26 lines]

 

[abourg8646 via AccessMonster.com]

I got everything working. One of my problems was when I create a SQL
statement then it would automaticly put brackets in areas that I didn't need
then and this would give me a syntax error. Is there a way to fix this so
that it wont add brackets on its own.

I have this in a spreadsheet and I have links going to all these different
sheets filling in data. The problem with the speadsheet is that if you change
a link in one sheet it changes in all sheets. I have taken advice from quite
a few people and I really appreciate all the help. Right know I have my
report working. What I need know is to pull it in by date. I have two SQL
statements that I'm working with. The 1st is for my schedule and it works.
The 2nd is for my stats and I'm getting a syntax error in parameter clause:
1st SQL
PARAMETERS [[Forms]!frmDate![txtEndDate]] DateTime;
TRANSFORM First(Query1.[Time Wkd]) AS FirstOfTime
SELECT Query1.[Employee Name]
FROM Query1
GROUP BY Query1.[Employee Name]
PIVOT "D" & DateDiff("d",[Date wkd],Forms!frmDate!txtEndDate) In ("D6","D5",
"D4","D3","D2","D1","D0");

2nd SQL
PARAMETERS [[Forms]!frmDate![txtEndDate]] DateTime;
TRANSFORM Max([Stats Query].[MaxOfTime Wkd]) AS [MaxOfMaxOfTime Wkd]
SELECT [Stats Query].EmployeeID, [Stats Query].Expr1003
FROM [Stats Query]
GROUP BY [Stats Query].EmployeeID, [Stats Query].Expr1003, [Stats Query].
Expr1
ORDER BY [Stats Query].EmployeeID, [Stats Query].Expr1
PIVOT "D" & DateDiff("d",[Date wkd],Forms!frmDate!txtEndDate) In (“D30â€,
â€D29â€,â€D28â€,â€D27â€,â€D26â€,â€D25â€,â€D24â€,â€D23â€,â€D22â€,â€D21â€,â€D20â€,â€D19â€,â€D18â€,â€D17â€,
â€D16â€,â€D15â€,â€D14â€,â€D13â€,â€D12â€,â€D11â€,â€D10â€,â€D9â€,â€D8â€,â€D7â€,"D6","D5","D4","D3",
"D2","D1","D0");

I don't see the difference.

It sounds like it would be easier for you to do what's being required by
using a spreadsheet rather than a relational database.

[quoted text clipped - 9 lines]