architecture question

[philvalko]

This starts as an architecture question and then gets into a form question.

I work with a database of neighborhood indicators (health, crime, etc). Basically, I have a series of data tables; each table holds time-series data for a single indicator or a few related indicators. For each indicator, I have a primary key that is related to a table of IndicatorIDs. The IndicatorID table of course has an "IndicatorID" field and an "IndicatorName" field with text describing the indicator. Two of the indicators are "value of houses sold" and "# of houses sold". Up to this point I have been structuring tables with the following fields:

"ID" (autonumber), "IndicatorID", "neighborhood", "year", and "value/count"

As for the housing sales data, I run into a problem (I think) b/c I shouldn't put "value sold" in the same field as "# sold", right, because one is currency and one is count? So, my thought was that I would have two fields: "value" and "count", but the problem with this is that I could only have one IndicatorID when in fact I have two seperate indicators.

My next solution was to create two separate tables. No problem. Easy as pie. However, I would like to have a single form that enters data into both tables at the same time: users would select the neighborhood, the year, and then add values for "value sold" and "# sold". Now, I'm lost. Really not sure how to proceed. Any insight?

 

[daherb]

As for the housing sales data, I run into a problem (I think) b/c I shouldn't put "value sold" in the same field as "# sold", right, because one is currency and one is count? So, my thought was that I would have two fields: "value" and "count", but the problem with this is that I could only have one IndicatorID when in fact I have two seperate indicators.

I don't see why only having 1 indicator id would affect you being able to have a seperate field for "value" and "count" at the end of the day it is the "ID" field i.e th PK that will hold this information together.

My next solution was to create two separate tables. No problem. Easy as pie. However, I would like to have a single form that enters data into both tables at the same time: users would select the neighborhood, the year, and then add values for "value sold" and "# sold". Now, I'm lost. Really not sure how to proceed. Any insight?

To do this simply use a form with a subform (a wizard can do this pretty much for you just tell what fields you want in the form). There are better ways to do it which are more complicated but i would start with doing it like that first, just make sure you have done your table relationships before you start.

 

[philvalko]

Thank you for the insight. Conditional formatting is exactly what I need. I have a follow-up question:

Now I have a table with 4 fields: [IndicatorID], [NeighborhoodID], [Year], and [Value]. The [Value] field contains data for "# houses sold" and "value of houses sold" indicated. The first three fields together serve as a multi-field primary key. I would like to create a query that produces a table view with 5 fields: [IndicatorID], [NeighborhoodID], [Year], [# houses sold], and [value of houses sold]. Essentially, I would be selecting the [value] field where [indicatorID] = X and appending to a selection of ALL where [indicatorID] = Y. I've been searching the web for the answer to this and trying a few different things with no luck so far. I apologize if this is obvious. I've been spinning my gears for a couple hours now.