Matching Apps to Sales

[Krystoff]

Hello all,

I am doing a cleanup project that has got me kinda stumped. I need to attach a sale to an app that doesn't have anything connecting the two. Here is what I have.

I have an application that was "split" at the time of creation. We will use ice cream as an example.

If John Doe wanted an eskimo pie and an ice cream cone. The application process split off a duplicate app that had one product in either app. The problem now is that later, we get what actually sold and I need to hook that up to the app. IE if John only bought the eskimo pie, I need to hook that up to the correct application.

Oh yeah, to make matters worse, there could be a time when they actually applied twice (Different than splitting as it was different days) and wanted two different products. I have a way to differentiate them on the app side but not on the sales side.

Is there an easy way of doing this? Or do I have to just plug away at it?

If you need any more information please let me know.

Chris

 

[oharab]

That sounds pretty awful, but I'm not sure what's happening!
The way I understand it is you have 3 entities: Person; Application; Sale

Ideally 1 person could have multiple applications some of which will lead to sales.
Some applications will have more than one objects, but these are entered as seperate applications, even though they are applies for in the same transaction.....
Yuck!

Am I right so far?

I hate sorting out other people's dodgy data schemas!
Send some more info about what data you have. If poss it might be good to post some real data (anonymized as needed) and the relationships between them.
Alternatively you could send me the data & I will look at it for you.

Cheers

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[vbajock]

Are their any ODBC drivers mixed in with the ice cream, or perhaps squeezed into the eskimo pies?

 

[oharab]

or maybe an api layer wafer?



b

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[vbajock]

Perhaps a Chocalate Covered SQL Server!

 

[Krystoff]

Wow lots of replies. Thanks guys!

I was working on this as I posted and after and I figured out a way to do it. Maybe not the best way but it works.

It sounds dodgy but its not really. Ill explain a bit better. The situation is the same but the names have been changed to protect ... well me from losing my job.

John Doe calls in a orders an Ice Cream Sundae. During the call, we sell him on the idea that he wants Nuts. Now Nuts and Ice Cream both count as seperate sales so the person who built this came up with a way that if they sold the nuts to John, it would duplicate the sale. So it might look like this:

SSN Name Date Prod Split ID
999999999 John Doe 10/15/2003 Sundae Nuts 1
999999999 John Doe 10/15/2003 Nuts 2

However if they want two sales then it could look like this

SSN Name Date Prod Split ID
999999999 John Doe 10/15/2003 Sundae Nuts 1
999999999 John Doe 10/16/2003 EskimoPie Nuts 2
999999999 John Doe 10/15/2003 Nuts 3
999999999 John Doe 10/16/2003 Nuts 4

Again, this is all apps. Then when they actually come into the store, they might only get an Eskimo Pie without nuts. The biggest problem I am having is that they didn't place the ID of the application into the sales portion because well they were stupid. The sales portion is a seperate database that had its own app id and they didn't care at that point if the sale was a split sale, they cared that it was a sale. Now they care.

So here is what I did, I made everything with a split filled out a 1 in a seperate field, and then anything that was blank is a 2. Then I created a unique ID that combines SSN & Prefill date (Long date) & ProdCount(the 1 or 2 I made). So now I can go about matching these seperately to the sale. And this time I am putting the ID in the sales portion.

Like you guys, I love cleaning up after people.

Thanks for your help, I think Ive got a handle on it now.

Chris