Search, populate and save

[eamon]

Hi

I have 2 tables: tblschools and tblbookings. tblschools has 16000 records and tblbookings is empty.

I want to have a form called newBookings with schoolName, postcode and contact (I want these to be populated from the tblschools) and 2 more fields (date and presenter) that are on tblbookings, which will be filled in by the user.

Once the form is filled in the user then clicks a save button and the record is saved to tblbookings.

I also need a text box to type in a postcode and the frmnewBookings is populated with school name, postcode and contact associated with that postcode. (after a 'search' button is clicked)

Just as design point I want the form look uniform, not a columnar part and data view part.

I have managed to do this with Libre office but it couldn't cope with searching 16000 records for a postcode. Therefore I have turned to Access 2016 but after weeks of trying I am not understanding how to do it.


If anyone can set me off in the right direction I would be delighted.

Cheers

Eamon

[June7]

Couldn't there be more than one school associated with a postcode?

Why not a combobox that displays school names? Type name into box to locate school.

Why would you duplicate school info into tblBookings? Just save the primary key.

Will you need a table for Presenters?

[eamon]

Couldn't there be more than one school associated with a postcode?

Why not a combobox that displays school names? Type name into box to locate school.

Why would you duplicate school info into tblBookings? Just save the primary key.

Will you need a table for Presenters?

Thanks for the reply,

Postcodes are the best way to search for a school, there are a few with the same postcode but there are many schools with the same name. Not sure what you mean by 'save' the primary key? Good call about the presenters, they could have data associated to them.
If I want to put a subform (data view) of all the bookings >=TODAY, would it not be easier to grab all the data from the tblbookings?

Cheers

[isladogs]

If you are referring to UK schools, there's a better way still to ensure uniqueness.
Each has a unique 7 digit number code which first groups by area e.g 802 then 4 digits to identify the school e.g. 4140
I expect other countries have a similar system.

Also if these are UK postcodes, I have example databases that may be useful to you. If so, let me know and I'll provide links.

[June7]

Save only the school record ID (primary key) into tblBookings (a foreign key field). This establishes a relationship between tables. Whenever you need the school info, build query that joins tables. Same for providers.

Basic relational database principle.

[eamon]

Thanks for the help, the penny has dropped about using primary and foreign keys. I have used a combo box. Works great. Thanks again June 7. I would be interested in seeing the dB for postcodes ridders52, good info about the code.
Regards Eamon

[isladogs]

I uploaded two example databases to another forum.
The first is a searchable UK Postcodes database https://www.access-programmers.co.uk...d.php?t=295233
You will need to download the postcodes data yourself from the supplied link as it's a large file

The second adds postal address searched for each selected postcode.
See https://www.access-programmers.co.uk...d.php?t=295378
This requires creating an online account with ideal-postcodes to download address lists as JSON files

Each includes geographical info and google maps centred on the postcode

I also have a commercial UK Postal Address Finder app with many additional features at http://www.mendipdatasystems.co.uk/