Select records from two tables with identical fields

[PrimaryKey]

Hello all.

I'm trying to return all address records for a given customer so that the user can select which address they want to use, but they're stored in two different tables. So I'm building a subform populated with a query that has a "Select" button the user can click to return the address info back to the orders form, but am unsure how to populate the popup subform with addresses that are located in two tables. Basically I want to use a SELECT statement to get the Street, City, State, Zip, and Country from the a CustomerAddress table, and a PropertyAddress table where ID (from Customers) = CustID (which is in both tables).



Is this possible? The fields names have the same and type in both tables.

[Bob Fitz]

I think you need a UNION query with a WHERE clause. See: https://www.w3schools.com/sql/sql_union.asp

[June7]

Do you intend to duplicate the address data in Orders table?

Why use two tables? Have one table with another field for identifier as Customer or Property address. Can't an address be both?

[PrimaryKey]

Do you intend to duplicate the address data in Orders table?

Why use two tables? Have one table with another field for identifier as Customer or Property address. Can't an address be both?

You know - that's a really good question. Can I ask you something first - do developers typically duplicate the address info in the Orders table? I only ask because by referring to an AddressID, if the address changes or is deleted, the Order record will change or lose the address. I was curious what is common practice there.

And to answer your question, right now I'm actually using one table - exactly like you suggested. See below the breakdown

• cust_Customers: main customer record, insteads fields like: ID, FName, LName, etc
• cust_CustomerProperty: stores customer property info, with fields like: CustID, Acreage, etc
• cust_CustomerAddress: stores addresses for 3 types, billing, shipping and property, fields are: ID, CustID, PropertyID (nullable), TypeID, Street, City, State, Zip, Notes, etc
• cust_CustAddType: ID, Type (as text)

My mainform is linked to the Customers table. I then have a subform CustomerAddresses linked to the cust_CustomerAddress table where users can enter the billing and shipping address. Then a subform for CustomerProperty where users can enter basic property info, and then a subform within that subform for CustomerPropertyAddress linked to cust_CustomerAddress. But that's where I run into problems. How do I make it so that:

1. A user can enter addresses for multiple properties as a single customer might have multiple properties
2. The CustomerPropertyAddress sub-subform only shows records for the relevent customer, and of the relevant type, which still adding the CustomerID and PropertyID?

Do you have any suggestions there?

[June7]

Two options if you must maintain history of address used for the order:

1. duplicate the address data into Orders

2. create a new record for the new address, add a field to flag addresses as 'active/inactive', this allows saving only the ID value

"Didn't seem like that worked." means what - error, wrong results, nothing happens?

[PrimaryKey]

June7 - I'm really sorry, I edited my post right after posting it because I realized I was vague. I kept getting a popup asking me for an ID #. I'm going to re-create it now (re build it) as I had just changed it over to a second table.

Just want to run this by someone like you (must more knowledgable than I) - I should be setting the record source of the sub-subform as a Select statement that selects records from cust_Customers with a TYPE of "Property" and CustID = CustomerID. Does that sounds right to you? Give me a little bit and I'll re-build and test it and let you know what problem I ran into.

[PrimaryKey]

So I got it to work, and I can add addresses for a given property, but they aren't attached to the property record. Could you help me understand better how I should set the record source for the CustomerPropertyAddress sub-subform? Right now I'm using a query. And I've linked that subform to the subform CustomerProperties on PropertyID and CustomerID. And I've linked the CustomerProperties subform to Customers main form on CustomerID.

Up until I did that I was getting an error saying that a related record was required in cust_Customers.

[PrimaryKey]

I got it working! But - how do I filter the sub-subform records to only show those with a Type = 5. I stopped populating it with a query, and am now populating straight from the cust_CustomerAddress table. Any idea?

[orange]

What kinds of Customers are involved?
I'm not sure what a Property address means.
I have worked on large company databases where we had
-physicalLocationAddr eg. 123 Pine St....
-mailingAddr eg. PO Box 3500...
-billToAddr eg. 200 OakPark Pkwy @CorpFinances.....
-shipToAddr eg. 67 Riverside Ave, Bldg #7......

and possibly others.

[isladogs]

Another option for the address history question is to have a StartDate and EndDate field.
The EndDate would be blank until a move occurs

[PrimaryKey]

I got it working everyone! I learned about a new feature - form Filtering on Load - that was what I was missing. I was trying to load the form record source from a Query - when I should have been using a filter.

Thank you all for you help and comments. I really, truly, appreciate it.