Merge linked tables!

[thaonguyen1013]

Hello,



I've linked 4 tables from Excel into Access. How do I go about merging these 4 tables into a master table and have it updates as the new values are being added to the original tables?

P.S. I've tried to append all 4 sources into 1 table, but it would not update. HELP

[June7]

The original tables will not 'update' a master table. Why would you need the 'master' table? Use queries to view data of related tables (in this case the tables are Excel links).

Describe the data structure and relationships and what you really need to accomplish. Why are you linking to Excel and not using Access tables?

[thaonguyen1013]

I need to track cases that I assigned to other people. Each of them have their own workbook in excel format, because of the formulas and conditional formattings being used. They're password protected to avoid changes made by other users. I want to have a master worksheet for easy tracking and reports. I've tried to link all 4 tables using excel, it updates previous entries but will not add new entries (I used the cell-cell linking method).
So now, I'm hoping Access would enable me to do so. I've been playing it with for a while but I'm running into a dead end. Any suggestion?

Thanks in advance.

[June7]

If you only need to view the user's worksheets and not input data, then a query should work. Are these workbooks all identical in structure (same columns)? If so, I think you need a UNION query to 'merge' the 4 links into one dataset structure. The UNION will be dynamic so that changes made to the workbooks will feed through the links. There is no designer or wizard for UNION, must type in the SQL View of query designer. There is a limit of 50 lines. Example if all 4 workbooks have columns with same names and same order:

SELECT "WB1" AS Source * FROM workbooklink1
UNION ALL SELECT "WB2", * FROM workbooklink2
UNION ALL SELECT "WB3", * FROM workbooklink3
UNION ALL SELECT "WB4", * FROM workbooklink4;

If there is any variation in the structures, must explicitely name the fields in each row (be thankful for copy/paste). The first line will establish the fieldnames for the merged set. Hence the alias Source in the example.

Now can use this query for filtering/sorting/searching all records and as basis for forms/reports.

Be sure to verify the query results. I just did a test with a linked workbook and a table. When I put the workbook as first line in the UNION, not all records were retrieved. When I put it as second line, all records retrieved. So be aware that UNION retrieves only unique records unless the ALL keyword is used and because the ALL keyword is not on the first line and the workbook has duplicate values for the field I selected on, the total record count was less than the original set.

[thaonguyen1013]

Okie. I linked the worksheets into access, created union query. Everything worked as I wanted, but when I went back to encrypt the original worksheets. The query and the tables won't open in Access.

[June7]

What do you mean by 'encrypt'?

[thaonguyen1013]

Password protected.
Because the files will be on a shared drive containing personnel info, they have to be password protected. Is there any way to go around this?

[June7]

Review this, about halfway down to 'You Cannot Open a Password-Protected Workbook'
http://support.microsoft.com/kb/257819

[thaonguyen1013]

Yeah, I figured that's the only to make it work. It's such a pain to ask all users to open their files just so I can do what I need to do. I guess we just have to deal with that for now.

[June7]

The alternative is an Access-only (no Excel component) database, which makes more sense to me.

[thaonguyen1013]

How would I go about adding formulas to the table in Access? I'm not very good with writing codes. That's the only reason I went with Excel
I have a lot of if-then formulas in the Excel tables for a certain column. So say this is how my table looks like

Name | Initiation Date | 1st Reminder | 2nd Reminder | Status

In "1st Reminder", cases that's 5 days old from "Initiation Date" to change to "DUE", and 6 days to change to "OVERDUE"
In "2nd Reminder", cases that's 7 days change to "DUE", over 7 days to "OVERDUE", =>10 days to "TERMINATED"
Also, cases with "Completed" status to be replaced with X.

It would be AWESOME if you could figure this one out for me.

[June7]

Cannot do calculations in tables (unless you upgrade to Access 2010). Do calculations in queries. Use an expression to create a field in query. This query and its fields (constructed or natural) can be referred to just like a table.

Expressions you describe would be like:
1st Reminder: IIf([Initiation Date] < Date() - 5, "OVERDUE", "DUE")
2nd Reminder: IIf([Initiation Date] <= Date() - 10, "TERMINATED", IIf([Initiation Date] < Date -7, "OVERDUE", Null))

You want an X in Status column? This could be a Yes/No field. Just click to set value. What criteria determines 'Completed' status?

Calculations can also be done in textboxes on forms and reports.

Everything you are now doing in Excel can probably be done in Access, even control what records each user is allowed to work with. Access is the tool for managing data. Excel is a tool for heavy calcs, such as what-if scenarios.

[thaonguyen1013]

OMG! It works. This is so COOL!!!!
One more question . Is there a way to make a drop down menu in the text field?

[June7]

On a form that would be a combobox with a RowSource.

In a table in Design view, this would be on the Lookup tab. (Slap my hand for mentioning). I NEVER set Lookup in table. I want to see the real value in the field when I view tables. Developers normally do not build databases that allow users to interact directly with tables and queries nor allow them to do design changes by disabling developer tools. Look at http://access.mvps.org/access/tencommandments.htm

You are asking questions about very basic Access functionality. I suggest you get an introductory tutorial book like 'Access in 10 Minutes'. It will probably be a faster learning process than trying to pick our brains with another question every 5 minutes and maybe not even getting a response here. The tutorial will introduce you to what you need to know to be competent with basics. Spend a weekend with it, do some practice exercises, then start your project.

[thaonguyen1013]

I will do that this weekend. Thanks again for your help.