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 |
#11
|
|||
|
|||
number of records in form do not match number of records in qu
Hi Bernard: The situation was the other way around. The querie and the table
had both 66 records. The form only 59. I went back to the querie and double clicked the line that was connecting the matching fields, and then all of the sudden the querie did not display all the records! Based on your suggestion I checked that each record had a match in each of the tables that are connected, and that is where I found the problem. So I went back created a category in each of the tables so that each time we input a name there are no blank fields in the other tables. Thank you so much Bernard!!! Thank you all!!! "Bernard Peek" wrote: In message , Graciela writes Hi Jeff: I am sorry I am not that clear. It is difficult for me to explain it in technical language. I learned Access on my own. :-( The querie pulls information from tables that are related. One of the tables "Network Members" is the one that is telling me how many members I should have when I view the form. (I set the primary key to be the autonumber... counter). That is how I know I have 66 different people in it. I created a querie, because I needed to create a form to input information that will go to the different tables. The last record in the form (record 59) coincides with record 59 in the querie. The information on both is the same. What I mean is the all the names of members and their corresponding information are the same in both...form and querie. The form gets only to record number 59. The querie gives me additional names (which are in the network members table) (records 60 to 66) , but they are not in the form. I checked the tables to make sure there were no repeated entries. That was fine. Those records should be in the form. Please let me know if this clarifies it. OK. Let me see if I have this right. You have one table with people's names in. This has 66 records in it. You have another table. You have created a query that joins the two tables, but the query only has 59 records in it. It sounds as if you are expecting the query to have 66 records. For that to work each record in your people table would have to link to one record in your other table. That might not happen. The default way for a query to link two tables is to include only records where there is a matching record in both of the tables. So if you have only 59 records in the second table then the query will ignore the 6 records that don't have a match in the second table. There are several ways to fix the situation. You could create six new records in the second table, with keys that match the values in the People table. As you seem to expect a fixed 1:1 relationship between the two tables you could merge the two tables together and just put all of the fields in the merged table. Or you could go back to the design view of the query. Look at the line that connects the matching fields in the two tables. Double-click on it. You will see a form that asks you what records you want to see in the query. You may want to change the setting here to include all of the records in the People table whether there is a match in the other table or not. -- Bernard Peek |
#12
|
|||
|
|||
number of records in form do not match number of records in qu
Hi John:
I solve the problem manually based on Bernard Peek's suggestion. I am sending you the SQL text, because I don't know if the problem is truly solved. I have a feeling it is. Thanks so much. Here it is SELECT [Network Members].[Members ID], [Network Members].[Last Name], [Network Members].[First Name], Specialty.Specialty, [Network Members].Degree, [Network Members].[E-mail Address], [Network Members].[Business Phone], [Network Members].[Home Phone], [Network Members].[Mobile Phone], [Network Members].[Fax Number], [Network Members].[Business Name], [Network Members].Address, [Network Members].City, [Network Members].[State/Province], [Network Members].[ZIP/Postal Code], [Network Members].[Country/Region], [Network Members].Notes, [Network Members].Attachments, [Network Members].KOL, [Network Members].Title, Membership.[Association ID], Membership.[Profession ID], Profession.Profession, [Hispanic Associations].Associations, [Network Members].[Interested in working with Pharma] FROM Profession INNER JOIN ([Hispanic Associations] INNER JOIN (Specialty INNER JOIN ([Network Members] INNER JOIN Membership ON [Network Members].[Members ID] = Membership.[Members ID]) ON Specialty.[Specialty ID] = Membership.[Specialty ID]) ON [Hispanic Associations].[Association ID] = Membership.[Association ID]) ON Profession.[Profession ID] = Membership.[Profession ID]; "John W. Vinson" wrote: On Thu, 4 Jun 2009 14:21:01 -0700, Graciela wrote: Hi John: I went back, and I manually counted the people I have in the table (without looking at the autonumber)... and I do have 66 people. The last six people do not show up. Please open the query in design view; select View... SQL from the menu; and copy and paste the SQL text to a message here. I suspect some problem with a join, but without seeing the details of the query it's hard to say. -- John W. Vinson [MVP] |
#13
|
|||
|
|||
number of records in form do not match number of records in qu
Hi Clifford:
Thanks for the suggestion. I went back to see, but there were no filters. thanks! "Clifford Bass" wrote: Hi Graciela, My guess would be that there is a filter set on the form. While in design mode, open up the properties of the form and see if there is anything in the Filter property. If so, delete it. If not, there may be a user-applied filter. While viewing the form, click on the Advanced item in the Sort & Filter section of the Home ribbon, and then choose Clear All Filters. Or, make sure that the Record Source of the form is indeed the query and nothing but the query. Hope that helps, Clifford Bass "Graciela" wrote: Hi All: This is my 4th attempt to post. I created a querie in Access 2007 that works fine. I see all 66 records and the info I need. I created a tabbed form so we can input new records directly in the form. The problem is that my last record in the form is record #59. I am missing the last 7 records! When I go back to the querie... the last 7 are in the querie. I don't know what to do! Can anyone help? Thanks I truly appreciate it |
#14
|
|||
|
|||
number of records in form do not match number of records in qu
On Thu, 4 Jun 2009 17:46:01 -0700, Graciela
wrote: Hi John: I solve the problem manually based on Bernard Peek's suggestion. I am sending you the SQL text, because I don't know if the problem is truly solved. I have a feeling it is. Thanks so much. Here it is SELECT [Network Members].[Members ID], [Network Members].[Last Name], [Network Members].[First Name], Specialty.Specialty, [Network Members].Degree, [Network Members].[E-mail Address], [Network Members].[Business Phone], [Network Members].[Home Phone], [Network Members].[Mobile Phone], [Network Members].[Fax Number], [Network Members].[Business Name], [Network Members].Address, [Network Members].City, [Network Members].[State/Province], [Network Members].[ZIP/Postal Code], [Network Members].[Country/Region], [Network Members].Notes, [Network Members].Attachments, [Network Members].KOL, [Network Members].Title, Membership.[Association ID], Membership.[Profession ID], Profession.Profession, [Hispanic Associations].Associations, [Network Members].[Interested in working with Pharma] FROM Profession INNER JOIN ([Hispanic Associations] INNER JOIN (Specialty INNER JOIN ([Network Members] INNER JOIN Membership ON [Network Members].[Members ID] = Membership.[Members ID]) ON Specialty.[Specialty ID] = Membership.[Specialty ID]) ON [Hispanic Associations].[Association ID] = Membership.[Association ID]) ON Profession.[Profession ID] = Membership.[Profession ID]; You'll only get all the records if there are matching records in all of the tables - Profession, Hispanic Associations, Spcialty, Network Members, Membership. If *ANY ONE* of these tables has no matching ID you'll lose that record. You might want to look into Outer Joins - change the INNER JOIN to LEFT OUTER JOIN or RIGHT OUTER JOIN; this will display records in the preserved table (Left table or Right table respectively) even if there is no match in the other. -- John W. Vinson [MVP] |
#15
|
|||
|
|||
number of records in form do not match number of records in qu
Hi Graciela,
You are welcome. Glad to see the others were able to help you solve the problem. Clifford Bass "Graciela" wrote: Hi Clifford: Thanks for the suggestion. I went back to see, but there were no filters. thanks! |
|
Thread Tools | |
Display Modes | |
|
|