Very important question (Access2000)

Topic

New Reply

I have broker information in a table which is linked to two main tables that store data entered by the user. The user sometimes enters the broker code as SSB_BCP or SSB_BCP_, when I extract data for reports it is not extracting few records because of this.

I want to add SSB_BCP in a column and add another column next to it with SSB_BCP_ and have somekind of formulae (or link) to let access know that it sould look for broker code in both columns.

Is this possible? If yes how?
Don’t tell me my designs are poor coz I did NOT design this program, a professional of some kind did the honour of designing and creating the program.

Thanx

Reply | Quote

Replies

So if I understand correctly, your saying this is a input error?

You should be validating the broker code at time of input rather then trying to fix the problem of a incorrect code later on.

Jim.

Reply | Quote

Like I said I did not deign this program and I cannot go change the design coz its locked. I can’t change the records coz theres more than 4000 records. I can ONLY retrive them,

So if you could help me PLEASEEEEEEEEEE DO.
Thanx

Reply | Quote

If you unwilling/cannot change the design, then fixing up the data is the only solution. Write an update query to look for the extra character:

=”SSB_BCP_”

and update to:

“SSB_BCP”

Of course that does nothing for fixing the problem itself as it can easily happen again.

Jim.

Reply | Quote

I was reading about update queries and I don’t think I could use this to do what I want to do coz there are more than one broker code that I want to update, and there are more than 4000 records to be updated.

Do u think its possible?

Reply | Quote

You can either write multiple queries or you can call a function in the query and supply the right code. For example, the update to would be defined as:

=GetNewCode([BrokerCode])

and the function:

Public Function GetNewCode(strBrokerCode as string) as string

Select Case strBrokerCode

Case “AAAAAAA_”

Case “BBBBBB_”

End Select

That’s one way to handle it.

I don’t have enough info to give you other alternatives. For example, if their all the same length, then you could just check for the extra character on the end and strip it off.

or is it always a “_” on the end that’s the problem? If so, you could perform a criteria check on that and strip off the extra character.

The function though is a way to handle the translation of the codes on a case by case basis.

Jim.

Reply | Quote

I’ve attached an spreadsheet with this reply it has two columns, the first one contains some of the broker code that is stored in the tables and the 2nd column contains how I want the broker data to be.

SORRY GUYS, totally forgot about the attachment at the last minute. The attached file is created and used for a combo. Time to time they add new broker data to the attached table.
Thank you.

Reply | Quote

You’ll have to type a few examples. I don’t see any files attached to your last message and I wouldn’t know how to get them anyway as I rarely come here.

Jim.

Reply | Quote

there was no attachment on your last message

Reply | Quote

Attachment is there, PLEASE help if you can.
Thank you..

Reply | Quote

There are no attachments on your messages, so there isn’t any point in insisting it’s there. If you attach a file, you can’t preview the message or the attachment gets lost.

Reply | Quote

It is there coz I opened it JUST to make sure, but I’ve attached the file with this reply.
It’s been attached to the 7th post from the top.

Reply | Quote

I can see now that it’s there, but it did not appear to be a few minutes ago.

Reply | Quote

I would definitely take Jim’s approach of updating the records. You can put VBA into your function which simply looks for a trailing ‘_’ and removes it, for example.

Your data volume presents no problem. 4000 records is a very small number. I deal with databases with millions of records on a daily basis.

Reply | Quote

Thanx guys but I’ve solved the proble by creating an extra table with the codes from the input table and linking that to the table with the codes I wanted and then linking it with the contact details. Then I’ve created a query and used that with the report queries.

Its a long mission but I’ve done it without changing the data in the input tables.
And about the attachment , IT WAS THERE ALL ALONG.

Reply | Quote