Update query using 3 tables

[sjr1917]

Can't figure the SQL to UPDATE a table (tblAccomResv) with columns: AccomID and DtBooked. It will be an empty table.

I need to populate it with one record for each record in tblAccomodations (ID) by each record in tblEventDts (EventDts).

Like:
Room1 Dt 1
Room1 Dt 2
Room2 Dt 1
Room2 Dt 2
etc.

 

[jdraw]

I don't quite understand your tables. Here is a free data model on Hotel reservations
Take a good look a room_Bookings in the model.

 

[sjr1917]

I have the table structure set, just need a specific SQL statement to initially fill a reservations table (tblRoomsByDt).

Let me try to simplify the example.
Assume 2 populated tables:
tblRooms --> field RoomID (the only field that matters in this example)
tblEventDts --> field EventDt (will hold 5-14 records being the event dates)

I need an SQL statement that will APPEND records to the empty table tblRoomsByDt having fields named RoomID and EventDt such that tblRoomsByDt will have x records for each room in tblRooms where x is the number of EventDt records e.g., 10 rooms and 5 days = 50 records in tblRoomsByDt.

Like...
rec 1 -> Room1, Dt1
rec 2 -> Room1, Dt2
etc

 

[sjr1917]

OK, finally found enough on the internet to solve it:

First create a Cross Join query (I named qryRoomsXDt):
SELECT *
FROM (SELECT id FROM tblRooms) AS B, (SELECT eventdt FROM tblEventDts) AS C;

Then INSERT that query into the emptied tblRoomsByDt:
INSERT INTO tblRoomsByDt (RoomID, EventDt) SELECT id, eventdts FROM qryRoomsXDt

 

[MarkK]

I don't understand. If you can SELECT the data you need from existing tables, then you don't need to INSERT it, since, obviously, it already exists. Data that you INSERT should represent something unique, and should be uniquely identifiable in the single location in which it is stored. #Normalization #NormalForms

 

[sjr1917]

MarkK,
Good point. The solution I came to was for the sake of expedience. One table with all the data needed to drive a TreeView control displaying the reserved status of every room throughout an event, rather than a complicated query that would have to be run each time I needed the view.

Will review normalization and the suggested Hotel data schematic again.
Thanks