T
Trisha
We are working from a DOS-based flat-file database from
which data is extracted, tables are then created in SQL
Server, then linked to Access for report writing.
One table called Staff Services has one field that
contains four fields' worth of data. Its label
is "GrossBilling" and it is populated with fields for
TimeBilling, TravelBilling, SuppliesBilling and
TotalBilling for each patient's ID from the original
database. None of these are labeled, we just finally
figured it out by comparing fields in both databases.
When our analyst extracted what we thought would be only
GrossBilling, the other three fields came along uninvited.
Since we can't find a way to not extract all four fields,
we're now faced with "de-transposing" the data into a new
table where every first field appends into TimeBilling,
the second field into TravelBilling, the third into
SuppliesBilling and dropping every fourth field entirely.
Luckily, they are always in the same order for every
patient.
If anyone has ever had to address this issue and can point
me toward any documentation on this problem using either
SQL or VB/VBA, I'd appreciate it. There are over 8,000
records in this table (which would drop to around two
thousand if we could normalize it and eliminate the totals
rows), so doing anything manually is out of the question.
Much gratitude in advance,
Trisha
which data is extracted, tables are then created in SQL
Server, then linked to Access for report writing.
One table called Staff Services has one field that
contains four fields' worth of data. Its label
is "GrossBilling" and it is populated with fields for
TimeBilling, TravelBilling, SuppliesBilling and
TotalBilling for each patient's ID from the original
database. None of these are labeled, we just finally
figured it out by comparing fields in both databases.
When our analyst extracted what we thought would be only
GrossBilling, the other three fields came along uninvited.
Since we can't find a way to not extract all four fields,
we're now faced with "de-transposing" the data into a new
table where every first field appends into TimeBilling,
the second field into TravelBilling, the third into
SuppliesBilling and dropping every fourth field entirely.
Luckily, they are always in the same order for every
patient.
If anyone has ever had to address this issue and can point
me toward any documentation on this problem using either
SQL or VB/VBA, I'd appreciate it. There are over 8,000
records in this table (which would drop to around two
thousand if we could normalize it and eliminate the totals
rows), so doing anything manually is out of the question.
Much gratitude in advance,
Trisha