appended data not visible in queries (1 Viewer)

theseus · Aug 6, 2018

I have a huge database that tracks my company's employee training. As of mid-July it was 154k records. The data comes to us as a .csv file. After importing and normalizing the data, I end up with 7 tables and a master table with lookups to the others.

I can easily append the data to each individual table. However after appending the new data it is not appearing in the queries that we use to isolate data. I am at a loss as to why the data isn't linked. I am sure I have forgotten something as it has been close to 20 years since I last worked with Access.

Thanks for any ideas you can give me...

jdraw · Aug 6, 2018

Can you post a copy of the database --remove confidential info or anonymize it -- with a few records to show the issue? You will have to post it as a zip file because of your post count.

theseus · Aug 6, 2018

Here is an zipped file with the structure and a sample of the data as I get it. I have to strip out a lot of data because of security.... Table names and relationships are all the same.

Mark_ · Aug 6, 2018

Welcome to AWF!

When you say it is not appearing in queries, I take it you've already made sure your append worked as expected? If so, check in the criteria for the queries to see if they are excluding your appended records for some reason.

This could be something as simple as "They are looking for dates after X" and you've not appended the date field they are looking in OR they have a foreign key used to link data together that isn't being filled in.

theseus · Aug 6, 2018

... they have a foreign key used to link data together that isn't being filled in.

I don't think that table 2 is being updated and I am not sure how to go about that when I append the data.

When I go to the individual tables, I can see the data there. However when I run a query the new data doesn't show. I did not include most of the queries just the two most basic. However even these two queries don't show the new data.

My problem seems to be that while I have appended new data to each table the db doesn't know it's related.

Gasman · Aug 6, 2018

So what is the new data.?

theseus · Aug 6, 2018

Gasman said:
So what is the new data.?

The new data is formatted just like the original data, but contains new assignments that were made after the db was created. It should not have new departments but may have new student groups, assigners/creators, or courses. It would of course have new dates as well.

Gasman · Aug 6, 2018

theseus said:
The new data is formatted just like the original data, but contains new assignments that were made after the db was created. It should not have new departments but may have new student groups, assigners/creators, or courses. It would of course have new dates as well.

That might well be the case, but as I (and everyone else except for yourself) have no idea what that data is, how are we meant to see why it is not appearing in what query.?

Pat Hartman · Aug 6, 2018

There does not appear to be anything wrong with the database you posted.
You didn't post the import code so we can't tell if the problem is with that. Every time you import a new set of data, you might have new values for the lookup tables.

Recommendations:
1. Do NOT use table level lookups. These will present problems when you create queries and write VBA. They are a crutch for people who don't know how to write queries and do nothing except obfuscate the actual contents of a table.
2. Create properly constructed object names. Use only the letters a-z, A-Z, numbers 0-9, and the underscore. Never use embedded spaces or special characters and avoid using function and property names especially as well as any SQL reserved words. So names like "Year" will be problematic since Year() is a function. "Name" is the name of a property so what does Me.Name refer to? The name of the form/report object or some control named after a column in the form/report's recordsource?
3. Don't name all the autonumbers "ID". Use some meaningful name that indicates the table the ID is for.

theseus · Aug 6, 2018

Pat Hartman said:
There does not appear to be anything wrong with the database you posted.
You didn't post the import code so we can't tell if the problem is with that. Every time you import a new set of data, you might have new values for the lookup tables.

Recommendations:
1. Do NOT use table level lookups. These will present problems when you create queries and write VBA. They are a crutch for people who don't know how to write queries and do nothing except obfuscate the actual contents of a table.
2. Create properly constructed object names. Use only the letters a-z, A-Z, numbers 0-9, and the underscore. Never use embedded spaces or special characters and avoid using function and property names especially as well as any SQL reserved words. So names like "Year" will be problematic since Year() is a function. "Name" is the name of a property so what does Me.Name refer to? The name of the form/report object or some control named after a column in the form/report's recordsource?
3. Don't name all the autonumbers "ID". Use some meaningful name that indicates the table the ID is for.

Thanks for the advice. When I rebuild the db I will implement it...

As for the import code. I have been importing the new data into a separate table then running an append query like below for each original table.

Code:

INSERT INTO Assignments ( [Assignment Name], [Assignment Type], [Assignment Effective Date], [Assignment Start Date], [Assignment End Date], [Assignment Due Date], [Updated By], [Created On], [Updated On], [Administrator ID Created By], [Administrator ID Updated By], [Assignment Visibility] )
SELECT july.[Assignment Name], july.[Assignment Type], july.[Assignment Effective Date], july.[Assignment Start Date], july.[Assignment End Date], july.[Assignment Due Date], july.[Updated By], july.[Created On], july.[Updated On], july.[Administrator ID Created By], july.[Administrator ID Updated By], july.[Assignment Visibility]
FROM july;

The_Doc_Man · Aug 6, 2018

Do you get any error messages when you import? If there are any lookups and there is a "relational integrity" issue, the import might fail for key violations of the lookup tables or some similar reason.

theseus · Aug 6, 2018

The_Doc_Man said:
Do you get any error messages when you import? If there are any lookups and there is a "relational integrity" issue, the import might fail for key violations of the lookup tables or some similar reason.

Nope no error messages at all. That's part of my frustration.

The_Doc_Man · Aug 6, 2018

Understood but had to ask.

appended data not visible in queries (1 Viewer)

theseus

Registered User.

jdraw

Super Moderator

theseus

Registered User.

Attachments

Mark_

Longboard on the internet

theseus

Registered User.

Gasman

Enthusiastic Amateur

theseus

Registered User.

Gasman

Enthusiastic Amateur

Pat Hartman

Super Moderator

theseus

Registered User.

The_Doc_Man

Immoderate Moderator

theseus

Registered User.

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread