hard to explain in few words but...

[russi]

Hi.

I have a table with a seprate record for each client in the system. Each record contains the following fields:

ssn, wage amount 1/2000, wage amount 2/2000, wage amount 3/2000(etc)


HOWEVER, another state does the same thing differently.
They make each calendar qtr for each client a different record. So that their records have:

ssn, wage amount, 1/2000

The same client could have another record
ssn, wage record, 2/2000


IS THERE any way to get their data into my format (the first one mentioned.

Hopefully not too confusing.

Russ

ssn

 

[mission2java_78]

huh?

 

[Mile-O]

Even better would be for you to move to their style as that is more consistent with database design.

Currently, from the way you have described your version of the database, you have a layout that would look more at home in an Excel spreadsheet.

Basically, you should normalise your tables - try searching for these keywords/phrases on this forum to give you a fuller understanding:

"Normali*ing"
"* Normal Form"

 

[mission2java_78]

I type too slowly

I think too slowly

 

[The_Doc_Man]

You have a non-normalized record there, pardner.

ssn, wage amount 1/2000, wage amount 2/2000, wage amount 3/2000(etc)

contains what is called a "repeating group" - which is a no-no in relational databases.

This should be

tblWageHist
- SSN (part of prime key)
- StartDate (other part of prime key)
- Coverage (could be as simple for Y=yearly, Q=quarterly,M=monthly,W=weekly, other codes that tickle your fancy.)
- wages

Then you would have one entry per time period and as many entries as the person with that SSN has been known to the system.

IS THERE any way to get their data into my format

Yes - but doing so would give you unnormalized data. Makes programming and querying and other things SO much nastier every time another year rolls around - or quarter or whatever period is involved.

 

[cdacey]

Russi,
I am not sure I completely understand what your asking, but I would break the base table down by the three fields (as detailed as needed), and then create queries that produce the data in the format you need combining fields when needed.

However, this is only good if you are supplying data not if you are receiving data.

Anyway, just my thoughts

Thanks Chris

 

[russi]

Hi! Thanks all for the input. And, Pat, I also type slowly.

Anyway, I should have been clearer, but both tables come to me from different agencies.
So that I have no control over either one, as it comes to me.

The difference being that one format you said should be changed is one I have had to work with for a year, so I already had queries designed to import the data into my own database tables.
And I was hoping not to do much to re-build. BUT I should have known better.

Thanks, again.

Russ