Thank you for taking the time to report an issue.
What's wrong... Please write below.
After my first attempt resulted in failure (thanks Galaxiom for informing me I needed to normalize my data), I spent the next few hours reading a large number of posts trying to figure out what normalizing meant.
So. here's my attempt at normalizing the data. Any advise about my tables and relationships would be hugely appreciated as I now realise the importance of gettingthis step correct before spending hours making the forms.
Partly as an experiment to test some new software I found, I have constructed this presentation [LINK] which goes some way to explain the reason you should seriously consider normalizing your data.
I believe the path forward must lie more in understanding our business needs, what we want our application to be capable of doing, rather than slavishly following the mantra of Normalize, Normalize, Normalize.
I have searched this site without much success, however, does anyone out there have any experience designing an ADB for tracking insurance certificates for compliance with subcontract requirements?
I have data for 100 survey studies in excel that have already been broken down into 3 datasets. Study# uniquely identifies each survey, and is the same value across the 3 datasets. I washoping to be able to link all three data bases based on study#.
Below, I outline what is in each dataset, how I think I should be normalizing the data, and at the end describe how I think to combine the 3 data sets:
each study can have multiple responses for country, audience and database, In excel it would look something like:
So I began grouping the headings first into sort of a related fields and the idea is to create a table base on the groupings and linked them together in Access (Relationship)
This is what I have come up so far Normalizing the headings.By the way it might be worth mentioning this. When I was looking at the Data on the Excel file. I have noticed that the Batch Number and Container columns have more Data on it. Meaning the Shipment on that day did not only contains more Batches of Item, but also has more Containers.
I am really hoping someone here can kindly help me putting this together.
The process I'm going through is:
1. Import the raw data into an "import" table that matches the structure of
the source data. Also included in the import table are columns for foreign keys of 'normalizable' fields, which are set to 0 when the source data is imported.
2. Append any new lookup data that may be present in the source file to the
3. Run a series of update queries on the import table to update the foreign
key fields with the keys of the lookup data. Depending on the source data file, there are between 3 and 7 of these update queries.
4. Append new records into the data table using only the foreign key values
I'm discovering that the update queries in step 3 are taking a LONG time to run (several minutes each),
- are there other, better processes or data structures to use?
- is there a way of optimizing update queries?
I am now storing all companies (whether vendor, customer or any other) in one list.
In other words there is no longer a"tblVendors" or a"tblCustomers", there are now Vendor Agreements and Customer Agreements that each reference the mother table"tblCompanies".
Likewise all contacts in one list. And in both cases all of them have daughter tables for any data that can ever change,
So the table tblContacts can only have"FirstName","LastName","DateOfBirth", and if needed SS#, that's it. Everything else is in daughter tables.
There are a lot of problems with this.
1. Every time you are going to add a new customer or a new vendor you must look both the tblCompanies and the tblContacts to make sure those names don't already exist and if they do, are they duplicates etc. etc. The list goes on and on...
2. You must find a way to limit queries to one phone number, one email, one address, etc. etc.
I can't tell you how much extra work this has caused and how many unforeseen data management issues; and users finding ways to do things you would have not dreamed possible.
The purpose of the make table query would be so that I could append the newly created table to an existing table.