Access 2010 Form With Strange Table Relationship


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?
Who is Participating?
JonahGrimmConnect With a Mentor Author Commented:
*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.
<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?
JonahGrimmAuthor Commented:
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.
<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
JonahGrimmAuthor Commented:
Disovered the answer on my own as the experts were hunting - and it didn't require code!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.