Access 2010 Form With Strange Table Relationship
Posted on 2011-04-23
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?