Stupid Link Question

[dls61721]

I linked an Access 2007 table to an excel spreadsheet and ran into a problem.
I built a form with information from both the Access form and Excel
spreasheet. However, although the Access table has over 700 records, since
linking the two the Access form now only shows 400 records, while the table
still shows 700. Where have I gone wrong?

 

[KenSheridan via AccessMonster.com]

My guess would be that the form is based on a query which joins the Access
table to the linked Excel spreadsheet, and there are only 400 rows in the
latter matching rows in the former. If so you'll need to change the query's
JOIN from an INNER JOIN to either a LEFT JOIN or a RIGHT JOIN depending on
which side of the join the Access table is. You can do this in query design
view, or by switching to SQL view and changing the type of join used. This
will then return all rows from the Access table along with date from those in
the spreadsheet which match. If there are rows in the spreadsheet which
don't have matches in the Access table the data from these won't be returned
however. To return data from all rows in each regardless of matches you'd
need a UNION query which tacks together the results of LEFT and RIGHT JOINs.

Ken Sheridan
Stafford, England

 

[Jeff Boyce]

We aren't there. We can't see what you're looking at.

When you "link" in Access, you don't actually have a copy in your Access
file... the link is more of a view of the data that lives elsewhere. So you
don't have two tables (one in Access, one in Excel) -- just one in Excel
that you can see from Access.

If this isn't what you've done, please describe your situation in more
detail...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dls61721]

I do know that I have only one table and that is an access table. It is
linked to a spreadsheet. I have not run a query. It is a simple form that
shows data from the access table (name and date of hire) and a spreadsheet
(phone number). However, although there are 700 employees in the access
table, when viewing from the form there are only 400 employee records. I'm
guessing that the spreadsheet only has 400 employees with phone numbers,
hence the return of 400 records in the form. How can I see all 700 employees
using the access form, regardless of whether there is a phone number or not?

 

[KenSheridan via AccessMonster.com]

As I said in my earlier reply you need to either LEFT JOIN or RIGHT JOIN the
Access table to the linked Excel spreadsheet in the query. I'm not familiar
with the Access 2007 interface for this but in earlier versions, in query
design view you'd select Join Properties from the View menu and in the
dialogue select the 'Include ALL records from 'YourAccessTable' and only
those records from 'YourExcelSpreadsheet' where the joined fields are equal'
option.

Ken Sheridan
Stafford, England

I do know that I have only one table and that is an access table. It is
linked to a spreadsheet. I have not run a query. It is a simple form that
shows data from the access table (name and date of hire) and a spreadsheet
(phone number). However, although there are 700 employees in the access
table, when viewing from the form there are only 400 employee records. I'm
guessing that the spreadsheet only has 400 employees with phone numbers,
hence the return of 400 records in the form. How can I see all 700 employees
using the access form, regardless of whether there is a phone number or not?

We aren't there. We can't see what you're looking at.

[quoted text clipped - 19 lines]