Adding multiple records 1

[TrishMcK]

Hi ,

I am setting up a database to hold classroom bookings for a school. I have a table called bookings which holds date, period (a number 1-7 denoting time) and staff id for the staff member booking that room. At the start of the year I need to populate the database with all the standard bookings for the year. E.G TM has booked Room 1 every monday period 1 for the whole year.

In previous years I have set the data up using a fill down in excel and just copy and pasted into access but wondered if I could automate this in access.

So in short what I want to be able to do is enter 1 record and get access to fill in the other 51 dates, 1 per week with same staff id and period number but increasing the date 1 week at a time.

Is this possible? without using VB?

Trish Mck

 

[Remou]

Hi
This may work for you. Set up a table called, say, WeekNo, with 52 records and one numeric field. The numeric field should contain the numbers 0 to 51. Then create a query like this (SQL view):

INSERT INTO BookRoom ( BookDate, StaffMember, RoomNumber )
SELECT #1/1/2006#+[WeekNo]*7 AS StartDate, "Mr Maths" AS StaffMember, 15 AS RoomNo
FROM WeekNo;

I have called your main table BookRoom for the purposes of this example. It has three fields: BookDate, StaffMember and RoomNumber.

 

[TrishMcK]

Thanks once again for first class information...it works a treat.

TM