Hello Community:

I hope the following makes sense and I appreciate your guidance!

I’m working backwards from Excel (until I develop a better system) where I download entries from two WordPress forms; ATCSurvey and GetPaidSurvey. The fields in common include FN, LN, Address, City, ST, Zip, DOB, Tel, E-mail, CatName, BrandName and the stuff WordPress adds: Timestamp, LastUpdated, ID, Key, CreatedBy, UpdatedBy.

They capture similar information but GetPaidSurvey captures more detail. ATCSurvey is a face to face survey with consumers. GetPaidSurvey is an online survey only. I added a column in both spreadsheets to concatenate FN-LN-DOB and make it the primary key in tblConsumerInfo. tblGetPaidSurvey captures additional details like BrandFeatures and BrandUsage.

I started by creating tblATCSurvey and tblGetPaidSurvey in Access and importing all corresponding data from the data dump. Then I built tblConsumerInfo using FN-LN-DOB as primary key.

I started building the corresponding tables with primary keys to make it a relational db:
tblATCSurvey – ATCID, FNLNDOB, FN, LN, Address, City, ST, Zip, DOB, CatName, BrandName
tblGetPaidSurvey– GETPAIDID, FNLNDOB, FN, LN, Address, City, ST, Zip, DOB, CatName, BrandName, BrandFeatures, BrandUsage

tblConsumerInfo – FNLNDOB, FN, LN, Address, City, ST, Zip, DOB
tblConsumerDetails – DETAILSID, FNLNDOB, Tel, E-mail
tblCategory – CatID, CatName
tblProducts – ProdID, CatID, ProdName
tblBrands – BrandID, CatID, ProdID, BrandName
tblBrandFeatures – BFID, CatID, ProdID, BrandID, FeatureDesc
tblBrandUsage – BUID, CatID, ProdID, BrandID, UsageDesc
tblZipCodeLatLon – ZipCode, City, ST, County, Lat, Lon

These are some things I’m trying to solve for.

I need to combine both tables into one – I created queries for tblGetPaidSurvey and tblATCSurvey with matching fields and required criteria. I’m looking into a union query to combine them into a long list. Consumer A can be in one table or both tables. About 10% of consumers in tblATCSurvey exist in tblGetPaidSurvey. If the consumer is in both tables, then I want to merge their information so that tblGetPaidSurvey overrides/take precedent over what’s in the other table. Can’t have duplicates.

Tag a subset of records to deliver to customer - I deliver a subset of the data to various customers based on each customer’s required criteria. I’ve delivered two batches to one customer. They are saved in separate spreadsheets. As the database grows, I need to query the master list and select a subset of all records that meet said criteria. I need to tag (CustID, ProjID, CritID, DeliveryDate) that subset and save it before I deliver the records so I can remove them from the master list during the next pull.

I suppose I need the following, at a minimum:
tblCustomerInfo – CustID, CustName, CustAddress
tblProject – PojID, CustID, PojectName, ProjDetails, ProjStartDate, ProjEndDate
tblCriteria – CritID, CustID, ProjID, CriteriaDesc
tblTaggedDataDetails – DetailsID, CustID, PojID, CritID, DeliveryDate, Data fields from Union Query above?

Thank you in advance for your help, questions and advice!