join 2 or more link tables & include all rows

  • Thread starter Thread starter ib
  • Start date Start date
I

ib

I have excell spreadsheet for various credit cards (table a, b & c), and
checking account (table d), and want to join automatically so I can run
reports and track categories. The queries join only the common entries,
while I need all rows. I like access ability to edit the spreasheets
directly from within access. Any ideas which could help me? Thanks.
 
I did some more research and this seems to work, or be one way to do it:

SELECT ALL *
FROM Qry1TblAmexCards
UNION SELECT ALL *
FROM TblCitibank74
UNION SELECT ALL *
FROM TblCitibank30
UNION SELECT ALL *
FROM TblCitibank92
UNION SELECT ALL *
FROM QryTblChecking
UNION SELECT ALL *
FROM TblMisc;

The issue now is that with single tables or queries, I could edit the record
and the change would update the linked file (excell spreadsheet). Now I can
not edit the records, any ideas on how to help me would be appreciated?

Thanks
 
What version of Access are you using? Microsoft took away the ability to edit
Excel spreadsheets while in Access due to a patent infringement problem. You
either have a very old version or Access; haven't updated your version to the
latest service pack; or maybe they added it back into Access 2007 and I
hadn't noticed.

Union queries, and some others, are not updateable. So you will need to go
to the individual tables as they are set up now.

In database design having a table for each year, business, ect., is a very
bad idea. Your table names like TblCitibank30 lead me to believe this is what
you have. You would be much better off combining all the similar tables into
one with some identifying column to show the BankNumber. Of course then you
lose the ability to edit the Excel spreadsheet.
 
Access 2003 (11.6203.6568), they don't permit updates according to IT and
block it with our firewall... thanks for your help
 
A query with a GROUP BY, with an aggregate in the SELECT clause, or with
UNION is NOT updatable.


Vanderghast, Access MVP
 
Can you suggest a method which is updatable?

Michel Walsh said:
A query with a GROUP BY, with an aggregate in the SELECT clause, or with
UNION is NOT updatable.


Vanderghast, Access MVP
Click to expand...
 
Back
Top