number of records in form do not match number of records in querie

[Graciela]

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

 

[Graciela]

Hi Steve:
the form displays two tabs "tab controls" In one I see the "personal info"
in the other the "professional Info"
Thanks

 

[Jeff Boyce]

Graciela

I'm not sure from your description, but there's a chance that you are trying
to make sense of the number that shows at the bottom left of a form. That
"navigation" information merely tells you the number of records, not the
"record #".

Access stores data in tables as if it were a "bucket o' data", with no
inherent "record #". As soon as you create a query or otherwise filter the
data in a table, you get a "different" set of data.

Does your table have an actual "RecordNumber" field defined, or are you
using that record count as if it were a record number?

Or have I misunderstood your description?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graciela]

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.
Thanks

 

[John W. Vinson]

(I set the primary key to be the autonumber...
counter). That is how I know I have 66 different people in it.

That's the problem, I'm guessing.

An Autonumber IS NOT A COUNTER.

It has only one function: to provide a guaranteed unique meaningless
identifier.

If you have entered 66 records and deleted 7, you will have 59 records in your
table but the highest autonumber value will still be 77.

If you've even STARTED to enter a record, and hit <ESC> after the first
keystroke, cancelling the addition, an autonumber value gets used up and
permanently discarded.

It sounds like you're assuming that an autonumber value of 66 means that there
are 66 records in the table; it does *not* mean that!

 

[Graciela]

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.
Thanks

 

[Bernard Peek]

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.

 

[John W. Vinson]

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.

 

[Clifford Bass]

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]

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!!!

 

[Graciela]

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];

 

[Graciela]

Hi Clifford:
Thanks for the suggestion. I went back to see, but there were no filters.
thanks!

 

[John W. Vinson]

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.

 

[Clifford Bass]

Hi Graciela,

You are welcome. Glad to see the others were able to help you solve
the problem.

Clifford Bass