combining selective data from 4 calender tables in access in one result per day

[LtdLUk]

So, I have 1 database with 4 tables who contain meeting room reservations. Now I actually would like to display this on my coldfusion server on one page for the four meeting rooms displaying just for that specific day the reservations. This need to contain the date, time_start, time_stop, name. How easy is this done?
so if it's 10/2/2017, I want it to display (by refreshing the page or pushing a button) the reservations of that day, and so on...

[ranman256]

link in the 4 tables,
make a form with a textbox , txtDate , to enter the date to pull,
make a UNION query to pull all the data for that 1 day from the 4 tbls,

select * from table1 where [date] = forms!myform!txtDate
union
select * from table2 where [date] = forms!myform!txtDate
union
select * from table3 where [date] = forms!myform!txtDate
union
select * from table4 where [date] = forms!myform!txtDate

[LtdLUk]

I have clearly been to long out of access... I don't have a clue even how to link the 4 tables...

[LtdLUk]

so I have made the union, all info is now in 1 qry, now on the cfm, I need to figure out a way to display the daily meetings... this will be the hardest part? Since I need to display it properly.

[ranman256]

What does properly mean?

[LtdLUk]

That it needs to be clear in which meeting room it is... since it's a union field, it collected all the info. so I need to have all data nicely displayed with above or something the info from which meeting room

[aytee111]

What is the output going to look like? By day containing all meetings? By meeting containing all dates?

[LtdLUk]

What is the output going to look like? By day containing all meetings? By meeting containing all dates?

The idea is to have per meeting room the overview of meetings for that specific day
EG :
M1 - meeting over bla - start time - end time
M1 - meeting over bla - start time - end time
M2 - meeting over bla - start time - end time
M2 - meeting over bla - start time - end time
M3 - meeting over bla - start time - end time
M4 - meeting over bla - start time - end time

[aytee111]

Here's what is known as a Q&D - quick and dirty! It doesn't look pretty, you can do that part.

[LtdLUk]

Here's what is known as a Q&D - quick and dirty! It doesn't look pretty, you can do that part.

so i have come to the final stage where I would need my form (on which the query is based) to refresh automatically when data is put in online into the database. I read about requery stuff but this is not working. Please help.
Private Sub Form_Activate()
Me.Refresh
End Sub

[aytee111]

You need an event to happen in order to refresh the data (requery). What would the event be? Adding data to a table does not trigger an event. You could put in a timer and keep checking for new data, that would be one way to do it without user intervention. It would slow your database down, but maybe this doesn't matter.

[LtdLUk]

it's just a meeting room dashboard presented on a screen at the reception. that's really the only purpose. So what needs to be done, is when data is entered via the web application into the database that this data is automatically refreshed in the query on the form. All help appreciated.

[aytee111]

Timer event is the only way then, if you want it to happen automatically. Add an event procedure to the OnTimer event of the form and set the timer interval to how often you want the event to happen (the value here is in milliseconds, so 1 second has a value of 1000). In the event procedure put the Me.Requery.

[LtdLUk]

Id did this, but no luck thus far... or do I need to put the requery to the form?
Private Sub Form_Open(Cancel As Integer)
Me.TimerInterval = 30000
End Sub



Private Sub Form_Timer()
Me.QRYInfoBoard_New.Requery
Me.Refresh
End Sub

[aytee111]

As I said, Me.Requery is all you need. Not sure why you need the on open event as well, you can do it in the form's properties.