How to merge separate tables

[ssanfu]

@June - The dB (zipped) is 20+ mb, so it can't be attached to a post.

[June7]

I can't even download that at home.

Could make copy - eliminate most data, remove unnecessary objects...

[ssanfu]

Only 11 tables, no other objects........

[June7]

Oh well, probably moot. I think the issue has been addressed and guidance provided. OP wants to un-normalize data for export to Excel. The consequence is repetitious data.

[RogueIII]

Oh well, probably moot. I think the issue has been addressed and guidance provided. OP wants to un-normalize data for export to Excel. The consequence is repetitious data.

Ok, in that case, how would you bypass the 2gb limit? I can't seem to run the query to merge the data without hitting that.

[June7]

There is no way to get around the 2GB limit and still retain all records in one dataset.

I've never hit the limit.

[ssanfu]

SQL Server Express?? It has a 10 GB limit. (And it is free)

[June7]

Okay, maybe there is a way around. Not a simple way but a way. Maybe not need SQLServer Express. Maybe just build the query in another Access db that links to the Access tables.

I did not realize a query could cause exceeding the 2GB limit. But maybe I misunderstand. If by merge you mean actually saving to another table then yes that could exceed. But a query should not.

So back to question of why save to another table? Export query.

[ssanfu]

Yes, it is just a split dB design. Link the Access FE to the SQL BE. When you link BE tables to FE, there is a "dbo_" prefix on all of the tables. I found code to rename the linked table names (remove the "dbo_"). I am converting a split Access FE/BE to Access FE/SQL BE. Once all of the forms/reports/code is working properly, I will start converting queries to SP. Should take a load off of the network since I won't be transferring the complete dataset to access for processing. Security is better & better multi-user capabilities.

[RogueIII]

Yes, it is just a split dB design. Link the Access FE to the SQL BE. When you link BE tables to FE, there is a "dbo_" prefix on all of the tables. I found code to rename the linked table names (remove the "dbo_"). I am converting a split Access FE/BE to Access FE/SQL BE. Once all of the forms/reports/code is working properly, I will start converting queries to SP. Should take a load off of the network since I won't be transferring the complete dataset to access for processing. Security is better & better multi-user capabilities.

I'm slightly confused. Are you linking it to the SQL? If not, would you be able to tell me what to do step by step? Thank you once again for the help!

[June7]

ssanfu suggested acquiring and installing SQLServer Express. I tried this once and gave up. Issues because I could not install on the network server.

Before going to that effort, first try my suggestion in post 23.