Can merely using a Form auto-populate Y/N field (not present in the Form) in another Table

[Sam]

I'm trying to develop a Database application for use in a Real Estate business.

I have 6 tables related in sequence using a PK/FK system.

The first table is a Units table which has details of all the saleable inventory in a project, like Size, Configuration, Wing, Floor etc. I have a Yes/No field titled Status, which indicates whether the Unit is Sold or Not.

The other tables are Bookings, Customers, Billing, Payments and Receipts. I have created many queries to manipulate and calculate data across tables.



I have created several Forms to keep updating the data in the Tables.

My question is - When a Bookings Form is filled to add data into the tables, the mere act/event of making entries (and Saving) into the Form should auto-populate the Yes/No field in the Units Table, without having to keep the Yes/No field on the form itself.

Is there some way to do it.

[CJ_London]

you shouldn't need the status field in the units table - the existence of a record in the bookings table is sufficient.

Either join the booking table with left join from the units table - a null value means status is sold (or not sold depending on it's actual meaning)

Or use a dcount function to count the number bookings for that unit - if >1 then sold or 0 not sold

[Sam]

you shouldn't need the status field in the units table - the existence of a record in the bookings table is sufficient.

Thanks for your inputs. These were my thoughts as well. Let me explore the second part of your advice. I'm sure I'll find my way around.

[Sam]

SELECT [01Units].UnitID, [01Units].Type, [01Units].Wing, [01Units].UnitNo, [01Units].Config, [01Units].CarpetAreaFROM 01Units LEFT JOIN 02Bookings ON [01Units].UnitID = [02Bookings].UnitID
WHERE (([02Bookings].UnitID = Null));

Using this but not getting any output. I have set Default Value for [02Bookings].UnitID (Foreign Key) to Null

[CJ_London]

implies you don't have any units without bookings - take away the criteria, add some relevant fields from 02Bookings , see what you get

[Sam]

implies you don't have any units without bookings

But there are.

Finally, through trial and error, I set the Join Type to #2 "Include ALL Records from '01Units' and only those records from '02Bookings' where the joined fields are equal." Now things are working.

Thanks CJ_London for your help.

[CJ_London]

I set the Join Type to #2 "Include ALL Records from '01Units' and only those records from '02Bookings' where the joined fields are equal

that is a left join

[Sam]

Got it. Sorry for being such a nerd.
Playing around a bit and learning each day.

[CJ_London]

Inner Join is a T-Sql term, in Access it is just a Join. Access SQL has a slightly more limited sql vocabulary to T-Sql

This is quite a good resource
https://www.w3schools.com/sql/default.asp

and this
https://support.microsoft.com/en-us/...f-14a5eb7902c8

[Micron]

CJ_London, you're not saying that Access sql doesn't use the words "Inner Join"?

[CJ_London]

I was but am wrong 😑 I was thinking of outer joins a left/ right inner joins. 😞