First time Form Problem with data from multiple tables and possible solution?

[FCT_Access]

Hello, I’m a new access user with a bit of a problem (and a potential solution) I wanted to run by you for advice, tips, suggestions, solutions, anything.

We have inherited some wonky data (incomplete entries, etc.) from the previous owners of the database, and are trying to make it more user friendly by creating a ‘lookup’ form which pulls together data from 3 different tables in an easy to read form, as we correct the wonky data, when possible.
The setup of the tables are; tblSites, which has general information (State_Number (the field which all of the tables have in common) Site name, park, county ID, etc.),
tblReports has information regarding reports that have been written on the different sites (there can be multiple reports to the same State_Number—different phases of excavation require different reports, which may have different authors, etc.)

tblAccessionLog is a log of the condition, state, and locations of archaeological collections. Once again, there may be multiple entries with the same StateNumber, as there may be multiple collections that come from the same site, etc.

Ideally, when using the form, the user would use a combo box (or type) the StateNumber, which would then populate the form with certain fields of data we have from that StateNumber from all of the tables.

The approach we tried was to use a SELECT query to pull data from the 3 tables into one query, and use the form to go off of the query. The thought was, in this query, the StateNumber could be repeated as many times as necessary in the query to match the number of multiple Records and Accessions. From there I could write code in VBA which would display the first entries of Report and Accession information and populate a box with the number of other records associated with that StateNumber; if the user wanted to look at the other Records or Accessions, they may click a button which would pull up another form which would display all of the Records and Accessions associated with that StateNumber. Alas that did not work, among the problems encountered were:

1) Some of the records were never issued a state number (which is not something we can easily fix), and the query associated the wrong data with the records that did not have a StateNumber (I think, because that is the only relationship between all of the tables, and it did not know where to put the data regarding null entries).

2) The query we used created several hundred duplicates of records, filling the combo-box with duplicates. I think the duplicates were caused because of multiple Reports or Accession records for the same StateNumber, and I am neither comfortable with, nor understand SQL to the level where I can craft a SQL query which would not eliminate some 2,000 of our 3,000 records which would normally be displayed (I just can’t get my head around nesting SQL conditionals based on variables in other fields, on different tables. As close as I have managed to accomplish over several hours of attempts is to simply eliminate any StateNumber which does not have atleast 1 entry from every table).

3) When the user first goes to select a StateNumber, a “Enter Parameter Value” message box pops up asking for one of the fields of data that is supposed to be shown. If the user leaves it blank and clicks ‘OK’, then the combo box is empty. But if the user types in anything at all, it seems to work as designed (sans duplicates and wrongly-associated data).



My potential solution would be to create a macro that would run VBA code(I am competent in VBA, unlike in SQL) which would do a ‘VBA-Query’ by sorting everything into the table, deleting duplicates, and salvaging all but around 700 records. Then the macro would run the form based off of the VBA-created query table. And anytime the user wanted to use the form, the macro would re-erase the table and re-run the VBA-query and re-launch the form.

I’m trying to learn as much as I can about access as fast as I can, so my question(s) to you are, given the situation, do you think there is an easier way to accomplish the main task? Does the database layout as described sound okay? Do you see a problem with the proposed solution? What solution would you try? And finally, do you have any clue what could have caused Problem #3? I have re-created the form twice, and the pop-up still shows up.

Thank you very much!

[ItsMe]

Are the duplicates in the tables or only appear in the query? I would avoid creating a macro and just keep everything in VBA modules id you need to do updates to tables.

You might want to practice with the Access query GUI using some copies of the DB. There are different types of queries that may move you closer to some solutions.

[Dal Jeanis]

Okay, I'm hoping, from your description, that what you have is queries written badly, rather than actual duplicate data.

For item 3, you probably have an item in your query that can't be resolved by Access (For example, a reference to a control that uses the wrong syntax, or a reference to a field that is no longer on the table.)

For item 1, you should be able to fix the problem with certain records not having state numbers. It's not tough - you'll create a form whose query source looks for records with "Null" in the state, and which allows the user to set the state correctly for those records.

For item 2, if you post the SQL here, we can parse it out and figure what's wrong with your join. bring up the query in design view, then switch to SQL view, highlight and copy the SQL, and post it here. put the word code in square brackets [] before the SQL and /code in square brackets after it.