Constructing Query for Linked Tables

[Mircea]

I'm hoping someone can help me here. I'm running Windows 10 with Access 365, which I'm guessing is the latest version of Access.



What I have:

1) 5 databases
2) Each database save one has due to file size limitations 19 tables. The 5th database has about a dozen tables.
3) Every table save one has exactly 1 Million records. The one that doesn't has a little less than 1 Million records.
4) Every table has the exact same structure and field names:

9 character short-text field (indexed unique no duplicates)
20 character short-text field
4 character short-text field
15 character short-text field
15 character short-text field
8 character short-text field
8 character short-text field

The only thing I want to do is search the data. I will never be adding, editing or deleting fields or records, and have no need for reports or forms, except possibly a query form.

As a test, I created a sixth database, which I call the Master and successfully linked every table in the other five databases to it.

The Master file with all of the linked tables won't let me use the filter function. The Query Wizard won't allow me to select more than one table. The Query Design Form allows me to select every table, but then in the boxes it will only allow me to select one field from one table, and not one field from multiple tables, and the box won't let me select multiple tables.

Is there any way to search all of the linked tables at once?

The Query doesn't need to display all fields, I just need to be able to enter search criteria for one field and then call up the records and display three other fields.

My fear is that using a single search criteria might bring up 1 Million or more records, and I don't think it could handle that. If I could enter criteria for two fields, then it would only call up 2 dozen records or less, and on rare occasions maybe a few dozen records, but never more than a 100 records.

I had the same problem with the individual databases. The Query Wizard won't let me select more than one table, and Query Design allows me to select all tables, but not the same field from multiple tables.

Is there any way around that?

[ssanfu]

Welcome to the forum.....

I think you are going to come up with another plan. With 4 databases, each with 19 tables and 1 database with 15 tables, that results in 88 linked tables.

If you try to search 1 field in each of two non-related tables, your SQL would look something like

SELECT Table1.Field1, Table2.Field1
FROM Table1, Table2
WHERE Field1 = 'Help";

Now try adding 86 more tables and 86 more fields......

Because the tables are not related, you have a Cartesian join between the tables.

From http://www.orafaq.com/wiki/Cartesian_join :
"A Cartesian join or Cartesian product is a join of every row of one table to every row of another table. This normally happens when no matching join columns are specified. For example, if table A with 100 rows is joined with table B with 1000 rows, a Cartesian join will return 100,000 rows."

Now try creating a Cartesian_join with 88 tables!. You do not have enough memory to hold the result set.


I don't even think a UNION query would work.

SELECT Field1 
FROM Table1
WHERE Field1 = 'Help'

UNION

SELECT Field1 
FROM Table2
WHERE  Field1 = 'Help';

Now add 86 more UNION statements.



Maybe convert to SQL Server.
SQL Server Express is free and has a max database size of 10GB (SQLSE 2012 and later).
You could have 1 table to search....

[Mircea]

Maybe convert to SQL Server.
SQL Server Express is free and has a max database size of 10GB (SQLSE 2012 and later).
You could have 1 table to search....

I probably should have mentioned I'm not really a coder. I do VB for Access and Excel, but that's it.

Anyway, I finally succeeded after a long nightmare.

I installed SQL Server and it did successfully transfer the Access files, but I didn't realize you needed a front end for it, and not being able to access the data, I thought I did something wrong and deleted it.

I started over, this time with the Server Management Studio, but could never transfer the data from Access to SQL Server. I spent 70-80 hours on it, from 2:00 or 3:00 in the afternoon until 2:00 to 7:00 AM working on it, but eventually gave up.

Then, I tried MySQL. That installed fine, but again I spent about 50-60 hours trying to figure out how to upload data and it never worked.

I tried MySQL for Excel, but it didn't work and apparently it has had problems for years and MySQL has never bothered to correct the problems.

Then I found Bullzip for Access. That did work. It did transfer the files from Access. Problem was after 6 hours it had only transferred 262,371 records. At that rate it would take about 1,920 hours to transfer all the records, so I abandoned that.

I tried to reinstall SQL Server, but it would never install, so I decided to do a Windows reset, even though all of my applications would be deleted.

Now I was able to successfully install SQL Server, but then I started getting a black screen after start-up.

It took about 3-4 days to figure out how to fix that, and ultimately I ended up installing the October 2018 Windows update, and that solved the problem.

Even though SQL Server was working, the only way to upload the data was using flat files, and it took about 8 hours to do that, but it did do it successfully.


For benchmarks, I have 84,735,016 records occupying a shade over 8 GB (8.062)

A simple select query

SELECT * FROM DeathData
WHERE Birth='mmddyyyy';

takes about 3:30 minutes to run.

A more, um, complex query

SELECT * FROM DeathData
WHERE Birth='mmddyyyy' AND FirstName LIKE 'xx%';

takes about 6 minutes to run.

Anyway, I'm just over the bleeding Moon about it, since it will save me a few thousand hours of work over the long term and the results are spectacular.


So, thanks for the suggestion. In the end, it all worked out, so all's well that ends well.

[davegri]

Mircea; that's my kind of person! Persistent! Good luck, bud!

[CJ_London]

not sure if you know about indexing, but if the fields you apply criteria to or are sorted are not indexed, then adding indexes will make a huge difference

[Mircea]

Mircea; that's my kind of person! Persistent! Good luck, bud!

not sure if you know about indexing, but if the fields you apply criteria to or are sorted are not indexed, then adding indexes will make a huge difference

Well, a lot of that was my own doing.

I didn't understand that every time you install SQL Server it creates an instance whether you name it or not. So, I had all these named and unnamed instances on my laptop and if you uninstall SQL Server, it does not delete or remove the named and unnamed instances.

I finally figured out if you go into the registry using regedit and look in software for MSSQL and then delete that registry entry and restart your computer, all the named and unnamed instances are gone and when you do an install of SQL Server it truly is a fresh install.

The data originally came in 3 GB and 4GB text files. I had to find software that would read text files that large and then broke them down into text files of 1 Million lines each, then imported those into Access and Excel (because I'm paranoid).

Then I exported the data from Access as fixed length text files, but SQL Server wouldn't recognized them.

I had to use the "ragged right" option.

When you look at the text files, they're definitely fixed length and not ragged right, but ragged right was the only way SQL Server would recognize it. Some text files loaded fine, but some had error messages, with some error messages saying data was truncated. I checked those records and the data was fine.

I deleted all the records, then imported them again, this time using the ruler to define the last column. That actually created 8 columns, and I had to marry the columns in the table to the columns in the text file and leave the last column on "ignore" and they imported flawlessly without error.

I looked at the text files and couldn't find anything. The CF/LR characters were where they were supposed to be.

Best I can tell, there were anomalous characters either in the original text files, or Access generated them when creating fixed length text files. I suppose if I had a binary or hex reader I could track them down, but it's not really worth the effort.

After I uploaded all the text files, I tried to create a primary key, but it wouldn't let me. It kept giving me error messages.

So, I had to delete the table and records, then recreate the table with a primary key, then re-import the data. That went flawlessly, but I did notice a slightly perceptible delay of maybe 15 to 20 seconds for each file. I'm guessing those are error-checking routines to ensure the data is unique.

The first column is actually digits, but I can't use an integer field, because it has leading zeroes and I need the leading zeros.

The last two columns are actually dates, but I can't use date fields, because they won't accept "00" as a valid day or month, and they won't accept "0000" as a valid year, so it's just easier to store them as string characters. In Access I would just use VBA code to convert the string to a date field and then manipulate it as I want, but I won't ever have to do that here, so it's not a big deal.

SQL Server does allow you to create certain indexed fields for searching strings, and I might use that option, but the time factor doesn't really bother me, so I don't know if I'll do that or not.