1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access Form Display related data

Discussion in 'Business Applications' started by Erm, Jul 4, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    I apologise in advance but although I was once fairly proficient in Access, I have had a baby and not touched it for over 2 years and am now using 2010 instead of 2003 and feel like my Grandma would trying to send an email!

    I have a really daft issue...

    I have a basic database, Client table, Treatments table and various lookup tables.

    I have a form to view/enter Client table records (frmClientandTreatments) twith a subform showing related Treatments. I store a Condition (e.g. Arthritis) in the clients table which is looked up from another table that provides the category (e.g. Muscloskeletal)...I want to have the category display on the Client form when a Condition is selected. How do I do this as the record source of the form is the Client table? I tried setting up a query as the record source but themn the combo boxes didn't seem to work and it wouldn't let me add new records...I am confused!

    Also, should I create relationships to lookup tables? Enforce referential integrity?
     

    Attached Files:

  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I am not surprised that you are Confused. Access 2010 &2007 do things completely different to 2003, and not necessarily better that it makes life very difficult.
    I haven't looked at your database yet but I would suggest that you actually need 3 tables.
    Client table
    Condition Table
    ClientCondition table, which has a 1 to many from the other 2 tables, it would contain the ClientID and the ConditionID key fields.
    That way you can have more than one condition per client.
     
  3. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    It really is like starting over moving to 2010! Glad it's not all me! =D I have Now store primary condition and condition 2,3,4. This is always going to suffice but I do a count of each Condition Type in all these fields and am trying to get it to add up and it isn't working...this is the SQL
    SELECT lkpCondition.ConditionCategory, lkpCondition.Condition, [qryCountPrimaryCondition]![CountOfCondition]+[qryCountCondition2]![CountOfCondition2]+[qryCountCondition3]![CountOfCondition3]+[qryCountCondition4]![CountOfCondition4] AS TotalTally
    FROM qryCountPrimaryCondition, qryCountCondition4, qryCountCondition3, qryCountCondition2, lkpCondition
    GROUP BY lkpCondition.ConditionCategory, lkpCondition.Condition, [qryCountPrimaryCondition]![CountOfCondition]+[qryCountCondition2]![CountOfCondition2]+[qryCountCondition3]![CountOfCondition3]+[qryCountCondition4]![CountOfCondition4]
    ORDER BY lkpCondition.Condition;
     
  4. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    Here is the current version
     

    Attached Files:

  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Sorry, I can't work with Access 2010, can you save or convert it to Access 2007?

    I am not very good at analysing query in SQL mode, so I really need to see the query.
    It may be that you need a second query to do what you want.
     
  6. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    Thanks OBP, it says under options that it saves it as 2007 as standard...is it not opening?
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    No I have had this problem a few times now.
    I will see if I can change anything if not I will attach a blank Access database and you can import the objects in to it.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Nope, can't open it.
    Here is a blank Access 2007 database, can you open it and then import the objects from your database?
     

    Attached Files:

  9. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    Doing that now...
     
  10. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    Here it is...thanks again!
     

    Attached Files:

  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Nope, I still can't open it, I had this recently and can't remember how I got around it.
    maybe an Access 2000 database might do it.
    Try this one, ignore anything that might be in it..
     
  12. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    tried saving mine as 2000...didn't seem to change anything?! Should there have been a database on last post?
     
  13. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    I wonder if it's because I have it loading into Main menu?
    Here it is without...
     

    Attached Files:

  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Yes there should have been a Database attached to my last post. i have had that happen a few times now.

    Your last version is the same as the others, I think it is due to a setting, but I can't remember what it was.

    Here is a 2000 database.
     

    Attached Files:

  15. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    random....here it is...the qry is Condition Tally...thanks again!
     

    Attached Files:

    • OBP.zip
      File size:
      175.1 KB
      Views:
      4
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1059695