Combobox query IIF statement

[Stingone]

Hi all,

I have created a combox based upon a query.

1 combobox with days of the week
1 combobox with times refering to the days.

This works all fine and as soon i click on a day i get the list of associated times.

BUT what i want is.. as soon i have picked a day with a time and populate it in my record through the form, that the time just populated in the record will not be visable any more in the combobox. So if you populate the record with eventualy all times the box will be empty.

how can i accomplisch this?

 

[namliam]

Make an outer join in the recordset for the time-combobox

 

[Stingone]

Make an outer join in the recordset for the time-combobox

Can you give an example on how to do this and use it in a combobox?

Thanks in advance

 

[namliam]

Select time from yourTimetable a
Left join yourTargetTable b on a.time = b.time
where b.time is null

Something along those lines....

 

[Stingone]

Select time from yourTimetable a
Left join yourTargetTable b on a.time = b.time
where b.time is null

Something along those lines....

I Attached my database i really dont know where to start.

In the form you can choose the combination of a band incl podium incl a time/day. What i want is that as soon i have a combination of a time/day/podium the time of that podium is not visable any more. so at the end if you have planned everything the time dropdown is empty.

View attachment Planning.accdb

 

[namliam]

I seem to be getting a PHP document not a database in that attachemnt?

 

[Stingone]

I seem to be getting a PHP document not a database in that attachemnt?

??? Its the planning.accdb when i click on it, it downloads the database. Maybe your browser?

 

[namliam]

You would use this query:

SELECT [Time Query].Time, [Time Query].Day
FROM [Time Query] 
LEFT JOIN Event ON ([Time Query].Day = Event.Day) AND ([Time Query].Time = Event.Time)
WHERE (((Event.Time) Is Null));

Please take some time to review Naming conventions:
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=Name+convention

I wont go into the database too much but....
- Playtime, 20 minutes, really isnt that "usefull" or "proper" way of storing information
- Confining yourself to only 3 days or maximum 7 days by using Friday, Saturday, sunday, etc really isnt that "wonderfull", you may be better of using real dates
- Your "time" (which you use for a table and column name, is a reserved word, which isnt that great an idea to use. Then you are storing it as 11:00-11:45 as text. Instead consider using two columns, StartTime and EndTime
- In your event table you are storing Day and Time as well as Playtime. Instead of using the key value from your Time table.

I am sure if I dig deeper ... but yeah, loads of room to improve

 

[Stingone]

You would use this query:

SELECT [Time Query].Time, [Time Query].Day
FROM [Time Query] 
LEFT JOIN Event ON ([Time Query].Day = Event.Day) AND ([Time Query].Time = Event.Time)
WHERE (((Event.Time) Is Null));

I wont go into the database too much but....
- Playtime, 20 minutes, really isnt that "usefull" or "proper" way of storing information
- Confining yourself to only 3 days or maximum 7 days by using Friday, Saturday, sunday, etc really isnt that "wonderfull", you may be better of using real dates
- Your "time" (which you use for a table and column name, is a reserved word, which isnt that great an idea to use. Then you are storing it as 11:00-11:45 as text. Instead consider using two columns, StartTime and EndTime
- In your event table you are storing Day and Time as well as Playtime. Instead of using the key value from your Time table.

I am sure if I dig deeper ... but yeah, loads of room to improve

Many thanks for the awnser, only thing i dont see is the podium combination. it now disappears on all podium.

the database is still under construction and i will take your information with me to improve it

 

[namliam]

So you add the podium in a simular fashion as you did the day?

 

[Stingone]

So you add the podium in a simular fashion as you did the day?

I have 1 table with day/time. 1 table with day as primary key. 1 table with podium as primary key.

 

[namliam]

And you have a location and a form called events, your point is?

My point is/was you have done in your time query already with DAY what you want to do with podium, simply repeat that process?

 

[Stingone]

And you have a location and a form called events, your point is?

My point is/was you have done in your time query already with DAY what you want to do with podium, simply repeat that process?

I understand your point. But that query is based upon the table time with predifined time/day. What i want tot avoid is include podium in de table time and get a huge list. So the goal is that if podium 1 is booked from x-x podum 2 still shows the x-x time. Sorry i have been tot long out of access. But thanks for the support.

 

[Stingone]

Can anyone help on this one? i still have not solved this

Is the only way to do this adding the podium to the time/day table? and then getting a huge list?

Regards