Results 1 to 8 of 8
  1. #1
    Reh is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    5

    Question Duplicate records in Form view

    Hi, All!
    I have three main tables I am working with, WALL ID (the parent table), Condition assessment (child number one) and Treatment (Child number two). It is important to see the information for all three tables in one place, so I created a form based on WALL ID and inserted the condition assessment and treatment datasheets into the form.
    The relationships look like this:



    It works beautifully!
    However... I am having issues with duplicate records appearing, but only if I enter it a certain way. Here is the lowdown of the issues:

    If I enter into the condition assessment datasheet first, then treatment in FORM VIEW, it creates a double record.
    If I only enter Condition and leave treatment blank in FORM VIEW, it does not show the record at all when I close it and come back to it.
    If I enter Treatment first then condition in FORM VIEW, it only creates one copy, which is what I want.

    What's the problem D=

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,753
    What do you mean by 'inserted the condition assessment and treatment datasheets into the form' - you have form/subform arrangement?

    It is not clear from your diagram what the relationship types are - one-to-many, many-to-many? If either of these is the case then these 3 tables cannot be joined in the same query without seeing the 'duplicate' records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Reh is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    5
    Yes, subform.
    It is one to many. Why does it make a duplicate record?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    as june said, without knowing the one to may relationship of your tables it's hard to tell it looks like you can have multiple ConditionaAssessments and Treatment records for each WallID but you are not linking the conditionassessments and treatments in any way so is it possible to have a treatment and no assessment? is it possible to have an assessment and no treatment (I would assume this is possible) if you were to link your treatments to a specific assessment you could do what you want. The only way I can think to accomplish what you want will only work if you have one conditionassessment and one treatment record per person without that link.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,753
    Show the RecordSource SQL statements of the form and subform(s).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Reh is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    5
    There are multiple treatments and assessments for each WALL ID, but the assessments and treatments will not match up record for record. i.e. there is no specific treatment related to any specific assessment.
    It is possible to have an assessment and no treatment, but probably not the other way around.
    So I guess it would be fine if the subform creating the duplicates when it is enterred first was condition assessment =/ for some reason it works fine when I enter treatment first and then the assessment.

    Form recordsource: SELECT [WALL ID].*, Treatment.Wall_ID AS Wall_ID_Treatment FROM [WALL ID] INNER JOIN Treatment ON [WALL ID].WALL_ID=Treatment.Wall_ID;

    I can't see the recordsource field for the subforms, but the source object for both are based on the tables. Should it be based on a query instead?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,753
    Subforms are just forms or tables that have been positioned on a form. The SourceObject is a property of the container that holds the form or table. I have never used a table as a SourceObject, only other forms.

    This is my best quess for your situation. The RecordSource for the main form cannot be an INNER join of tables. The main form should be bound to WALL ID table and then Treatments and Assessments should be separate subforms synchronized to the main form by the Master/Child link properties.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I agree with June, I don't think you want this query as a record source for your data entry. You'll want to break it up into a subform for condition assessments and a subform for treatment. If you link both the subforms to the parent record correctly the display of multiple records should vanish.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. duplicate tables in relationship view
    By digitaldb in forum Access
    Replies: 3
    Last Post: 07-20-2011, 11:52 AM
  2. Show only some records in Form View
    By Douglasrac in forum Forms
    Replies: 3
    Last Post: 05-15-2011, 11:01 AM
  3. Hide records from Form View
    By Douglasrac in forum Forms
    Replies: 9
    Last Post: 05-13-2011, 11:55 AM
  4. Replies: 10
    Last Post: 04-19-2011, 03:38 PM
  5. Replies: 2
    Last Post: 02-12-2011, 09:54 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums