Design question for a db that will need multiple city/states selected in one field

[NaomiC]

Hi,

I'm trying to create a database to track potential applicants and their resumès. My Candidate table will have, FName, LName, Email, Phone, Resumè(attachment), SRNumbers, and Notes. I also need a DesiredLocation(s) set up that includes City and State; and Desired JobTitle that includes the title, series, and band numbers.



The part I am having a really hard time wrapping my head around is the Desired Locations and Desired Job titles may have more than one selection. For example a candidate may be interested in an IT position, HR position, and Analyst position in California, Texas, or New York. Is this possible?

[ranman256]

I did that EXACT thing. I have a 'picked' table. Dbl-click on the state, it runs an append query to add it to the picks. Join the picked table to your data to pull all those states.
Here is my example of a user pick list. The user can pick items from a list (dbl-click) the items are added to a Picked list table.
This table can be joined to the main table to pull ONLY the items chosen. (rather than have a query with many OR clauses)

---------------
QUERIES USED
---------------
qsAvail: (the available list to pick from)
select [ST], [state] from tStates

qsPicked: (all the items that have been picked in picked table)
select * from tPicked

qaAddPickedState: (append query to add 1 state selected to the picked list)
INSERT INTO tPicked ( ST, State )
SELECT [ST] ,[State] FROM tStates
WHERE ((ST)=[Forms]![frmPick]![lstAvail)

qdEmptyPikTbl: (erase all picks)
DELETE * FROM tPicked

qdDel1Pick: (delete 1 selected item in the picked list via delete key)
DELETE * FROM tPicked where [ST] = forms!frmPick!lstPicked

---------------
EVENTS
---------------
dbl-click AVAILABLE ITEM list:
runs qaAddPickedState to add to picked list

[NaomiC]

Thanks. I like your form but i'm not sure if I understand how to implement it. It looks complicated. Can it be made smaller so it fits into my current entry form? I'm also unsure about the part where you tie it into your main table.

[June7]

Normalization guidelines would call for related tables to store the multiple locations and jobs. This is essentially what a multi-value field does. Even though you don't see them, Access maintains related tables for the multiple selections. https://support.office.com/en-us/art...C-6DE9BEBBEC31

I never use multi-value fields.

ranmans's form is not tied into the main table. It saves records to a 'temp' table (table is permanent, records are temporary). It is a tool for filtering records, not for entering data to main table.

[NaomiC]

FANTASTIC! I think this is what I need.