help please with lookup function

[Chris Barnett]

hi. i'm a teacher needing some help probably with a lookup function of some
kind - but i'm not sure if that's the best way.
I have created a database which has a query of selected pupils - the pupils
are all identified by an 'Admin No'.
I have also created a report based on this query - 'Notification' report.

As well as their name, year and form I also need to put on this report the
room number of where the pupils are located. This information is held in a
'Rooms' table.

However the location changes depending on the day and we work on a two week
cycle.

Hence the Rooms table consists of the following fields

Admin
RedMon
RedTue
RedWed
RedThu
RedFri
BluMon
BluTue
BluWed
BluThu
BluFri

I would be grateful if anyone could help me with any ideas about how to get
the information off the Rooms table to the Notification report depending on
the day.

Thanks
Chris Barnett

(e-mail address removed)

 

[Dirk Goldgar]

hi. i'm a teacher needing some help probably with a lookup function
of some kind - but i'm not sure if that's the best way.
I have created a database which has a query of selected pupils - the
pupils are all identified by an 'Admin No'.
I have also created a report based on this query - 'Notification'
report.

As well as their name, year and form I also need to put on this
report the room number of where the pupils are located. This
information is held in a 'Rooms' table.

However the location changes depending on the day and we work on a
two week cycle.

Hence the Rooms table consists of the following fields

Admin
RedMon
RedTue
RedWed
RedThu
RedFri
BluMon
BluTue
BluWed
BluThu
BluFri

I would be grateful if anyone could help me with any ideas about how
to get the information off the Rooms table to the Notification report
depending on the day.

I strongly suggest you revise the structure of your Rooms table. the
way it's currently set up, it has two different pieces of information --
cycle and weekday -- embedded in field names. As you've apparently
discovered, that makes it very cumbersome to query. A more "normalized"
structure would be like this:

PupilsRooms
--------------
Admin (numeric (?), foreign key to Pupils)
Cycle (Text (maybe): "Red" or "Blue")
DayOfWeek (numeric, value 1-7, where 1 = Sunday, 2 = Monday, etc)
RoomNo (foreign key to a table listing all the available rooms)

With a table structure like that, it's easy to find out what room a
student is in on any given day. And a report based on a query that
joins the Pupils table with the PupilsRooms table can easily be used as
the basis for a report that groups by pupil and lists, by cycle and day,
the rooms where that pupil can be found.

 

[Douglas J. Steele]

Link to it if you can, or import it to a temporary table if you can't, and
use INSERT INTO queries to populate your final table.

For instance, you could something like

INSERT INTO MyTable (Admin, CycleWeek, DayNumber, Room)
SELECT [Admin No], "Red", 1, RedMon FROM ExcelTable
UNION
SELECT [Admin No], "Red", 2, RedTue FROM ExcelTable
UNION
SELECT [Admin No], "Red", 3, RedWed FROM ExcelTable
UNION
.....
UNION
SELECT [Admin No], "Blue", 1, BlueMon FROM ExcelTable
UNION
SELECT [Admin No], "Blue", 2, BlueTue FROM ExcelTable

 

[Chris Barnett]

thanks for your reply - i think i can understand how that will work.

however i'm now stuck with another problem.

the room numbers are provided to me as an Excel table - with the fields:
Admin No, Red Mon, Red Tue, Red Wed etc

have you got any ideas about the best way of converting this table to the
ideal table. - Admin, Cycle Week, Day Number, Room
is it something to be done when importing into Access or should i use a
function of Excel.

There are 1400 pupils on role and it needs to be updated every 6 weeks so i
could do with a fairly automated method of conversion.

cheers
Chris Barnett
(e-mail address removed)

 

[Chris Barnett]

so it's a couple of days late but just thought i'd say thanks for your
efforts in helping me solve my problem. i've now managed to get the table
converted and got the database linking to the correct day and working a
treat

thanks for your help
Chris Barnett

Link to it if you can, or import it to a temporary table if you can't, and
use INSERT INTO queries to populate your final table.

For instance, you could something like

INSERT INTO MyTable (Admin, CycleWeek, DayNumber, Room)
SELECT [Admin No], "Red", 1, RedMon FROM ExcelTable
UNION
SELECT [Admin No], "Red", 2, RedTue FROM ExcelTable
UNION
SELECT [Admin No], "Red", 3, RedWed FROM ExcelTable
UNION
....
UNION
SELECT [Admin No], "Blue", 1, BlueMon FROM ExcelTable
UNION
SELECT [Admin No], "Blue", 2, BlueTue FROM ExcelTable


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

thanks for your reply - i think i can understand how that will work.

however i'm now stuck with another problem.

the room numbers are provided to me as an Excel table - with the fields:
Admin No, Red Mon, Red Tue, Red Wed etc

have you got any ideas about the best way of converting this table to the
ideal table. - Admin, Cycle Week, Day Number, Room
is it something to be done when importing into Access or should i use a
function of Excel.

There are 1400 pupils on role and it needs to be updated every 6 weeks so
i could do with a fairly automated method of conversion.

cheers
Chris Barnett
(e-mail address removed)

"Chris Barnett" <[snipped]> wrote in message
hi. i'm a teacher needing some help probably with a lookup function
of some kind - but i'm not sure if that's the best way.
I have created a database which has a query of selected pupils - the
pupils are all identified by an 'Admin No'.
I have also created a report based on this query - 'Notification'
report.

As well as their name, year and form I also need to put on this
report the room number of where the pupils are located. This
information is held in a 'Rooms' table.

However the location changes depending on the day and we work on a
two week cycle.

Hence the Rooms table consists of the following fields

Admin
RedMon
RedTue
RedWed
RedThu
RedFri
BluMon
BluTue
BluWed
BluThu
BluFri

I would be grateful if anyone could help me with any ideas about how
to get the information off the Rooms table to the Notification report
depending on the day.

I strongly suggest you revise the structure of your Rooms table. the
way it's currently set up, it has two different pieces of information --
cycle and weekday -- embedded in field names. As you've apparently
discovered, that makes it very cumbersome to query. A more "normalized"
structure would be like this:

PupilsRooms
--------------
Admin (numeric (?), foreign key to Pupils)
Cycle (Text (maybe): "Red" or "Blue")
DayOfWeek (numeric, value 1-7, where 1 = Sunday, 2 = Monday, etc)
RoomNo (foreign key to a table listing all the available rooms)

With a table structure like that, it's easy to find out what room a
student is in on any given day. And a report based on a query that
joins the Pupils table with the PupilsRooms table can easily be used as
the basis for a report that groups by pupil and lists, by cycle and day,
the rooms where that pupil can be found.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)