Table Structure Problem

[jimithing1980]

This one may be a bit complicated, but I'll try to explain myself as best I
can. I have several horizontal tables and several vertical tables in my
database. The horizontal tables are typical tables with a unique ID as well
as some specific information about whatever the table references (i.e. a
tblHouses table would contain the StreetAddress, cityID, etc.)

I also have 6 vertical tables, one for each data type. For instance, I have
a vertical table called tblEntityDates that has a unique ID, HouseID,
EntityNameID, and a Date (the EntityNameID links to a table that lists what
can be considered the field name). The reason for the vertical table is that
we want to track history of when any date changes. Also, there is a benefit
due to the fact that we store nearly 100 different dates for each House for
varying reasons but most Houses only need 20 of those dates populated.

As well as dates, we have many different currency values, numeric values,
Text strings, etc. that require the same degree of historical tracking and
have the same population issues.

What I need to do is populate an unbound form with these values but it mixes
and matched from the horizontal table and the vertical tables. I'm using an
SQL Server backend and an Access frontend but am NOT linking tables using
ODBC but rather using 100% ADO (considering ADP but the same problem exists).
We cannot use Crosstab queries in SQL Server and though I've found many
functions that will create crosstab-like results, I would need to perform
these crosstab SPs on all 6 DataType tables and concatenate them using temp
tables and a view. Problem with that is we have 150 different users who will
be running these functions constantly all day, so the processing power needed
for this would get out of hand.

Has anyone ever had a dilemma similar to this and what suggestions are out
there? I have been searching for a few days now and cannot find any
reasonable solutions thus far other than possibly hardcoding each individual
control to a specific SQL statement or retrieving 6 different recordsets on
the form open event and iterating through them to populate the form. I'd
like an easier less processing power solution if one is available. Thanks in
advance for any suggestions!

 

[Damian S]

Hi jimithing1980,

To harness the power of SQL Server, you want to be using Stored Procedures
(or pass-through queries, but probably SP's) to do the bulk of the work.

When you say you want to put data on an unbound form and it's getting
mix'n'matched, what exactly do you mean?

For instance, are you trying to show the history of changes to an item of
data? If so, try having the main record on the main form, and a subform that
lists the changes...

Please provide an example of what you are trying to achieve, and an example
of what's happening.

Damian.

 

[jimithing1980 via AccessMonster.com]

It would be difficult to post an example of what I have since the data is
very confusing - I've oversimplified it here (and it's still quite
complicated). I don't want the history reported to the user on a form, I
just want it stored. For instance, I'll have a table that looks like this:

------------------------------------------------------------------------------
--------------------------
| ID | SourceID | EntityNameID | Date | CurrentRecord | UserID |
| 1 | 1 | 1 | 1/23/07 | 0
| 25 |
| 1 | 1 | 1 | 1/24/07 | 1
| 24 |
| 1 | 1 | 2 | 1/23/07 | 1
| 25 |
------------------------------------------------------------------------------
--------------------------

These two records would be identical except the second record is marked
current and therefore would be the record the user would see. In the
background, management can see when a record is changed (we also have an
updated date for each record) and the user who changed it. No records ever
get changed (except the CurrentRecord field and the DateUpdated field) - only
added.

The problem is I want to use a crosstab-like query to view the data in the
following format:

--------------------------------------
| 1 | 1/24/07 |
| 2 | 1/23/07 |
-------------------------------------

Where only records where CurrentData = 1 and where the first field is the
EntityNameID. The number of different EntityNameIDs is completely dynamic so
I can't hardcode this. Moreover, it's a multi-user database and I cannot
create TEMP tables to handle the data as I said in the previous post.