We help IT Professionals succeed at work.

Access 2010 Form With Strange Table Relationship

JonahGrimm
JonahGrimm asked
on
289 Views
Last Modified: 2012-05-11
Okay.

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

and

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?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
<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?

Author

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.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
<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
http://support.microsoft.com/kb/328828

Glad you got it going

Author

Commented:
Disovered the answer on my own as the experts were hunting - and it didn't require code!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.