Is there a program out there that can be used to do Access Queries and have a UI as shown?

[judgedredd]

Hi davegri,

I am not proficient at working with Access so this is a steep learning curve for me and for my own sanity I am keeping things separate for the time being. The more proficient I become the easier it will be to wrap my head around merging different styles of tables into one database.

Again, thanks to all your inputs everyone!

[June7]

Whether you move the tables to one file or leave in multiple files, you still need to have a split database. That means all data is in backend file(s) and all code/forms/reports are in frontend file. The frontend will link to backend tables and work with the linked tables as if they were all in one file. You said there were only 3 or 4 databases so this is not so bad. If you do eventually merge to one database file, this means relinking tables.

How large are these 3 or 4 database files? Access does have a 2GB size limit.

[madpiet]

Hi davegri,

I am not proficient at working with Access so this is a steep learning curve for me and for my own sanity I am keeping things separate for the time being. The more proficient I become the easier it will be to wrap my head around merging different styles of tables into one database.

Again, thanks to all your inputs everyone!

Simple. Import all the data from one table into a new database. Add a single column to the table, something like (but this is T-SQL).

ALTER TABLE [DataTable]
ADD COLUMN [Source] VARCHAR(20);

In Access, you'd just go into the table designer and add the "Source" column and specify the data type (likely short text) and a size.
If you can post some fake data (like 2 records), I'm pretty sure I can bash out a quick example that you could use.



Then you can update that column after each import.

UPDATE DataTable
SET Source = 'SourceDatabaseName'
WHERE Source IS NULL;

if you alternate between importing and updating, it should be super simple. Then all your data will be in ONE database. otherwise, you'd have to do something like create a view/select query where you specify the database path (using IN) and UNION ALL all that stuff together so you get one long table.