Access 2010 Form With Strange Table Relationship

Posted on 2011-04-23
Last Modified: 2012-05-11

I'm working on an Access 2010 database with an odd table relationship.  Essentially, I have a table that stores statistical metrics on a given date  (Data_event) and another that stores all of the details for a given metric (Elementlist_Lookup).  They are related by the foreign key pair Data_event.EventId = Elementlist_lookup.ID.

I am trying to create a form in access that will:

a) Show all of the metrics that have been entered over a given date range that will allow the metrics to be edited


b) Show an entry row for any metric that doesn't have a matching data point over the date range.

I can use an outer join query to accomplish this when there is no data populated in the Data_event table, but if I have a metric entered in Data_event, the Outer Join won't return the row for that metric in any other period (For instance, if I have a # of Rehospitalizations data point for January 3, I can't see # of Rehospitalizations for Febuary 1 in the query if feb1 has no data yet).

I figured out a way to do multiple queries in series to get the data set I want, but then it's not editable.   I could do this programatically in Perl/PHP + MSSQL, but that's not an option and I'm terrible with VBasic.

I know it can be done, and I've seen it done before - but how in the world can I get an editable form?
Question by:JonahGrimm
    LVL 26

    Expert Comment

    <I'm terrible with VBasic.>
    That kind of limits the options :)

    If you can get the data into a data set, then it can be blown into a staging table.
    The form can then be aimed at the staging table.
    You'd then use queries to put the data back from the staging tables to the main tables.

    Does the logic of that sound workable with your skill set?
    LVL 1

    Author Comment

    The logic does, yes - but I'd need (if you can) a bit more detail of how to go from the form and my temp table back to the main database.

    What's odd is that I can get this entire thing to work in a form-with-subforms scenario.  Given that it's just a one-to-many link, it's not hard to see a single metric and all of its data points.  What makes this tough is that twenty to thirty different metrics are generated at a time, and the simply-generated forms don't make data entry of that many different data points easy.  If it were, say, a week's worth of data for a single metric, they'd be perfect.
    LVL 1

    Accepted Solution

    *blink*  Okay.  In all my banging on it in the meantime, I sorted this out myself.

    What I discovered is that Access 2010 will not let you do multiple joins, however, it will allow me to prequery a table, join that query to another table, and still edit the original source data - it doesn't lose the referents in the form.

    So, it worked something like this:

    Take the Data_event table, and write a query that returns ONLY the rows within our relevant date range.

    Put together another query that Outer Joins the Elementlist_Lookup table on the foreign key, which we've passed from the first query.  This required a manual edit of the raw SQL to relate the join - Access couldn't do it automatically.

    Using this new query, allow the wizard to build a Multiple Item form.  Voila!  Data entry works.
    LVL 26

    Expert Comment

    <What I discovered is that Access 2010 will not let you do multiple joins>

    That isn't true in any sense.
    You can certainly create multi-table queries.

    Multiple joins between two tables is very problematic.
    Multi-field primary keys are not something Access appreciates.

    There are multiple reasons that a query is un-updateable

    Glad you got it going
    LVL 1

    Author Closing Comment

    Disovered the answer on my own as the experts were hunting - and it didn't require code!

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now