Merge multiple records into one

[Guest]

Hi

I've inherited a rather complex database that hasn't been written using
normal form or thinking about data redundancy.

What i've got is a form that makes data entry a pain and increases the
length of time required to keep things up to date. What I want to do is sort
this out. What I can't do, nor have the time to do at the moment is amend
the reports and some of the financial methods.

To start with the user does not modify live data, a copy is taken and placed
in a temporary table as below.

I've normalised this table into the following format:

Key | Day | Activity | Dept | Room | Start Time |
001 Mon 0002 YS Hall 10:15
002 Mon 0003 YS Rm1 10:15
002 Tues 0005 YS Hall 10:15

What I need to do is get this format back into the original format below:
Room Dept MondayActivity MondayStartTime TuesdayActivity TuesdayStartTime
Hall YS 0002 10:15 0005
10:15
Rm1 YS 0003 10:15

I can get the database to produce single records for each Room and
Department, but what I need it to do is merge as many day records onto one
Room/ Dept record as possible.

Hope this makes sense, can anyone offer some help?

Thanks

Craig

 

[Guest]

Try this --
TRANSFORM First([Activity] & " " & [Start Time]) AS Expr2
SELECT MISofficer.Room, MISofficer.Dept
FROM MISofficer
GROUP BY MISofficer.Room, MISofficer.Dept
PIVOT MISofficer.Day;

 

[Guest]

Hi Thanks for this, this works well for displaying on screen. However, I
need to produce some printed reports that go out to our associated
establishments.

I've managed to get it to do the following, but really I need this
condensing into as fewer rows as possible.

I had contemplated using some kind of numbering system, but does anyone have
any thoughts?

Thanks

Craig

Try this --
TRANSFORM First([Activity] & " " & [Start Time]) AS Expr2
SELECT MISofficer.Room, MISofficer.Dept
FROM MISofficer
GROUP BY MISofficer.Room, MISofficer.Dept
PIVOT MISofficer.Day;

Hi

I've inherited a rather complex database that hasn't been written using
normal form or thinking about data redundancy.

What i've got is a form that makes data entry a pain and increases the
length of time required to keep things up to date. What I want to do is sort
this out. What I can't do, nor have the time to do at the moment is amend
the reports and some of the financial methods.

To start with the user does not modify live data, a copy is taken and placed
in a temporary table as below.

I've normalised this table into the following format:

Key | Day | Activity | Dept | Room | Start Time |
001 Mon 0002 YS Hall 10:15
002 Mon 0003 YS Rm1 10:15
002 Tues 0005 YS Hall 10:15

What I need to do is get this format back into the original format below:
Room Dept MondayActivity MondayStartTime TuesdayActivity TuesdayStartTime
Hall YS 0002 10:15 0005
10:15
Rm1 YS 0003 10:15

I can get the database to produce single records for each Room and
Department, but what I need it to do is merge as many day records onto one
Room/ Dept record as possible.

Hope this makes sense, can anyone offer some help?

Thanks

Craig

 

[Guest]

I do not follow what you want it to do. It produces one row per room.

Post an example of what the output should look like.

Hi Thanks for this, this works well for displaying on screen. However, I
need to produce some printed reports that go out to our associated
establishments.

I've managed to get it to do the following, but really I need this
condensing into as fewer rows as possible.

I had contemplated using some kind of numbering system, but does anyone have
any thoughts?

Thanks

Craig

Try this --
TRANSFORM First([Activity] & " " & [Start Time]) AS Expr2
SELECT MISofficer.Room, MISofficer.Dept
FROM MISofficer
GROUP BY MISofficer.Room, MISofficer.Dept
PIVOT MISofficer.Day;

Hi

I've inherited a rather complex database that hasn't been written using
normal form or thinking about data redundancy.

What i've got is a form that makes data entry a pain and increases the
length of time required to keep things up to date. What I want to do is sort
this out. What I can't do, nor have the time to do at the moment is amend
the reports and some of the financial methods.

To start with the user does not modify live data, a copy is taken and placed
in a temporary table as below.

I've normalised this table into the following format:

Key | Day | Activity | Dept | Room | Start Time |
001 Mon 0002 YS Hall 10:15
002 Mon 0003 YS Rm1 10:15
002 Tues 0005 YS Hall 10:15

What I need to do is get this format back into the original format below:
Room Dept MondayActivity MondayStartTime TuesdayActivity TuesdayStartTime
Hall YS 0002 10:15 0005
10:15
Rm1 YS 0003 10:15

I can get the database to produce single records for each Room and
Department, but what I need it to do is merge as many day records onto one
Room/ Dept record as possible.

Hope this makes sense, can anyone offer some help?

Thanks

Craig

 

[Guest]

I can get the output as follows

Room | MondayStart | MondayWeeks | TuesdayStart | TuesdayWeeks |
HALL | 10:00 | 11 | |
|
ROOM| | | 11:00 | 12
|
HALL | | | 13:00 | 12
|
HALL | 15:00 | 5 | |
|

However i'd like the output as follows, reducing the number of individual
lines by room type.

Room | MondayStart | MondayWeeks | TuesdayStart | TuesdayWeeks |
HALL | 10:00 | 11 | 13:00 | 12
|
HALL | 15:00 | 5 | |
|
ROOM| | | 11:00 | 12
|

Thanks in advance

Craig

 

[Guest]

This data does not match your orignal post. Also this output does not match
the crosstab query I gave to you.

Post your SQL and input data.

 

[Guest]

Hi

These are my efforts todate, simply giving a new record for each room type.
What I need to do is reduce the total number of room types and fill as many
rows as I can to reduce the number of pages printed.

Craig