If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Stupid Link Question
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? |
#2
|
|||
|
|||
Stupid Link Question
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 dls61721 wrote: 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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200909/1 |
#3
|
|||
|
|||
Stupid Link Question
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" wrote in message ... 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? |
#4
|
|||
|
|||
Stupid Link Question
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? "Jeff Boyce" wrote: 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" wrote in message ... 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? |
#5
|
|||
|
|||
Stupid Link Question
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 dls61721 wrote: 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] table still shows 700. Where have I gone wrong? -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|