creativeimages
Technical User
I have several data bases with 4,000 to 10,000 records (maybe more records) that I have imported to Access. Every month or so, I receive more information that totals thousands of records that I need to append to the different tables. The information is formatted differently, and in some cases, two o four records in the data received make one of my records.
I have two big problems that I think could be related. I think the solution is conditional decisions to either divide or join tables.
For example, a large table designed earlier by others has customer contact information, billing information, if different, general accounting and terms information and 10 fields for each of up to 6 products or services sold. My products and services are usually sold to a customer in a fixed location, and regardless of the future of the customer (move or out of business), the product and services usually stay at the location.
With time, a location can have different owners or tenants, different and multiple products and services; and with different suites or units, multiple owners and tenants per location.
I need to “break” some tables in to Customers / Tenants / Owners, Job or Location Addresses, Billing Addresses, and Products or Services Sold.
This brings me full circle to my problem. I need several “keys” by combining several fields to ID the customer, the address, the billing address, and the six product fields. Then I need to break up the tables.
One key that I would like to create is the combination of the street number, the street name, the street type, and the zip code. These fields combined would make unique ID for any address. Now associating this unique location or street ID to the customer, and the different products and services would allow the table to be reduced in size.
I would like to combine these fields, but I do not know how to this in Access. I think the approach would be to add a field to the table, and use the expression function to combine the fields with a concatenate function. That does not work. Why? I do not know or understand! With thousands and thousands of records, I do not know what to do.
I have two big problems that I think could be related. I think the solution is conditional decisions to either divide or join tables.
For example, a large table designed earlier by others has customer contact information, billing information, if different, general accounting and terms information and 10 fields for each of up to 6 products or services sold. My products and services are usually sold to a customer in a fixed location, and regardless of the future of the customer (move or out of business), the product and services usually stay at the location.
With time, a location can have different owners or tenants, different and multiple products and services; and with different suites or units, multiple owners and tenants per location.
I need to “break” some tables in to Customers / Tenants / Owners, Job or Location Addresses, Billing Addresses, and Products or Services Sold.
This brings me full circle to my problem. I need several “keys” by combining several fields to ID the customer, the address, the billing address, and the six product fields. Then I need to break up the tables.
One key that I would like to create is the combination of the street number, the street name, the street type, and the zip code. These fields combined would make unique ID for any address. Now associating this unique location or street ID to the customer, and the different products and services would allow the table to be reduced in size.
I would like to combine these fields, but I do not know how to this in Access. I think the approach would be to add a field to the table, and use the expression function to combine the fields with a concatenate function. That does not work. Why? I do not know or understand! With thousands and thousands of records, I do not know what to do.