Select Primary Key column based on two fields

[cbramsey]

So here is my situation, I have a two tables


SUBSCRIPTION and ADDRESS

The Address table has ADDR_ID (PK), STREET_NUMBER, STREET_NAME

The Subscription table has SUB_ID, ADDR_ID, SUB_DATE, SUB_EXP_DATE

The address table is pre populated from the county assessors office with all available addresses. On my form, when I am creating a new subscription I have the following fields

FIRST_NAME
LAST_NAME
EMAIL
These fields go to Name table, no issues here

STREET_NUMBER
STREET_NAME
SUB_DATE
SUB_EXP_DATE

what I am wanting to happen is to be able to select the STREET_NUMBER and STREET_NAME from ADDRESS table into the form and have this relationship pass the ADDR_ID from the ADDRESS table to the ADDR_ID in the SUBSCRIPTION table. I know this is fairly simple but my mind is not working and I need to get this done. Any help would be greatly appreciated.

[orange]

???
So here is my situation, I have a two tables
SUBSCRIPTION and ADDRESS...

These fields go to Name table,???

Show us your relationship window with tables and relationships.

[cbramsey]

[cbramsey]

address table
addr_id (pk, fk to subscription table)
street_number
street_name
drive_order

name table
name_id(pk, fk to subscription table)
first_name
last_name
email

payment table
payment_id(pk, fk to subscription table)
payment_method
payment_amount

subscription table
sub_id (pk)
addr_id (fk to address table)
name_id (fk to name table)
payment_id (fk to payment table)
sub_date
sub_exp_date

[orange]

Does the prepopulated ADDR Table have Names?
Does the prepopulated ADDR Table have duplicate STREET_NUMBER and STREET_NAME combination?
Do you need a Payment Date? or is it pay when you subscribe?

[cbramsey]

Prepopulated table does not have Names
It does contain duplicate STREET_NUMBER and STREET_NAME but not a duplicate combination if that makes sense.
There is no Payment Date as that is the same as SUB_DATE

[orange]

How(what is your procedure) do you assign Name/NameID to Subscription?

see this for composite unique index.

[cbramsey]

So when I populate the Subscription form

I enter the name and email information which populates the Name table and creates the NAME_ID PK with autonumber, same with Payment Table. The difference in the Address table is it is prepopulated with data so I am trying to figure the best way to pass the ADDR_ID column into the Subscription Table. All of the other tables populate perfectly.

One workaround I tried just to see if it would work was create a subscription_address table that worked similar to the Name and Payment Tables, and then join that to the Address table on the Street_Name and Street_Number fields, which worked and I can go that route, I just need to create a couple of extra queries for drivers to get the drive order from the Address table.

[orange]

Just thinking while typing:
My understanding is: your Subscription form is the Access Form---correct?

So you get a Person/Name via phone or some means; then you can/could compare their address with the prepopulated Addresses.
If there is a match, you could relate that Name to Address and put AddressID into the Subscription Table. Or add address info to your NameTable (create a Customer/Subscriber Table).
If there is NOT a match, then you ??? do what??. Add to your prepopulated list? Update your Customer/Subscriber Table?

What exactly is the purpose of the prepopulated Address Table? Is it directly related a Subscription to the County XX somehow, or is it a convenience to get addresses?

Subscription to what? (Looking for some context)

Do you need all the tables once you get set up? I can see Subscriber and Subscription tables in longer term, and only the Addr Table for set up convenience. Payment is related to Subscription and may be stored there?? New subscription each year?

[mike60smart]

Hi
Can you upload a zipped copy of your database with no confidential data?

[NoellaG]

what I am wanting to happen is to be able to select the STREET_NUMBER and STREET_NAME from ADDRESS table into the form and have this relationship pass the ADDR_ID from the ADDRESS table to the ADDR_ID in the SUBSCRIPTION table.

simple solution: put a combo box on your form. Store the ADDR_ID, but show the streetname or street_number + street_name concatenated in one field