Duplicate Records

bfriend5

Registered User.
Local time
Today, 12:09
Joined
Mar 23, 2015
Messages
22
Hi everyone,

I'm still quite new to using Access but I have a small database that I manage and the other day I realized that I have 90 records for my one and only form; however, there are only 19 records when I check the table. I really have no idea how or where to check to correct this error. I'm sorry this is vague but I'm just at a loss for where all the extra records could have come from. Any advice or guidance is very much appreciated. Thanks!
 
Is the record source of that form the table? Or is it a query?
 
The record source is from the table. I don't have any queries just yet (still working and learning about that part).
 
Can you post the database so I can see it?
 
Yes I can, I'll try to strip it and zip it shortly. Thank you for taking the time to check it out.
 
Here's the database. I have several tables that use each other but the central (main) table that stores the new information is tblSample. The one and only form I have is frmCaseOverview and that is where it is showing that I have 90 records, while when I check tblSample it isn't anywhere near that. Thanks again.
 

Attachments

The attachment is only a shortcut, not a database, so try again!
 
You were incorrect. The form's record source is a query and it included an INNER JOIN which was responsible for the duplicates.

Form's that you want to use to directly interact with data (add/edit/delete) need to be based on a table, not a query. My advice is to use a table for the form's record source and not a query. That will also alleviate the other issue you haven't discovered yet--you can't edit anything with that form.
 
Would you mind elaborating a bit further? I know my greenness to this program is showing, but I didn't realize I had based the form off of a query? When I first created the form I thought that just adding existing fields from the field list to the form would mean that any data entered into that field would be stored under that table? I realize my "design" is probably all over the place but it's my first shot at creating and managing a database in access so I realize that I'll have to probably re-do it a few times to get it to a workable final form.

Anyways, thank you for pointing that error out and for the nudge in the right direction, much appreciated!
 
If you go into Design View and look at the properties, there's one called Record Source. That tells the form where to get data from, this is what is in yours:


Code:
SELECT tblSample.*, tblPanelists.PanelMemberNumber FROM tblPanelists INNER JOIN tblSample ON tblPanelists.PanelistID_PK = tblSample.Panelists.Value;

In short, it means it will display all records from tblPanelists that have a matching record in the Panelist field of tblSample. Look at your data in tblSample and you have 90 Panelist values, thus 90 records.

I really don't know what you are going for, but an input form shouldn't be based on a query, but on a table. If you want to interact with more than one table, you need a seperate form for each.
 
Thank you. A few more questions though - when I'm looking at tblSample and at the panelist values in that table, do you mean that it is displaying a record based on every single panelist selected for each record? As in, since there were 5 selected for Request Number 1, and 5 selected for Request Number 2, and that's responsible for 10 records right there?

I think I understand that a bit more now. The main issue I had is that I was trying to create the relationships between tables by creating a look up value in tblSample (which is where I may have gone wrong?). My logic behind this was maybe a misinterpretation of one to many relationships. My thinking was that for everyone one record in tblSample I'll have many panelists, but it's a certain list of panelists that won't change so why not always refer to that list? With your advice it sounds like tabbed forms that are linked to one another is a better way to go if I am understanding you correctly?
 
do you mean that it is displaying a record based on every single panelist selected for each record?

Correct. I never new you could put together such a field (with multiple values), but Access is now allowing people too. It's bad practice. You should have a seperate table for the data in that field.

I use tabbed forms to seperate and group similar data together that all come from the same table. Most likely you will use some sort of sub-form. You use those when you have a 1 to many relationship in your tables.
 

Users who are viewing this thread

Back
Top Bottom