Relationships conflict with query

[Krash878]

This is a relationship problem.
I have a main table called tblChargeBack
I have a dictionary table called tblCBCodes
I have another dictionary table called tblCardType

TblChargeBack has about 15 fields but I am only concerned with 2 of them.

I made a qryChargeBack

The qryChargeBack is identical to tblChargeback except I have added two fields.
One field is based of the first digit from an original field. The original field is CBRSN.
So what I have in the query for the new field is “CardType: Left([CBRSN],1)”

There is another field that is based of an original field.
This time it is based off MerchantID which is an 11 digit number. I need the 4,5,and 6 digits so I have “CBID: Mid([MerchantID],4,3)”.

In the query this works fine.

The problem:
I am trying to make another query that will give me the definitions of the CBID and CardType.

When I set the relationship I can not make a one to many including the qryChargeBack. Is there a way to make the relationship or do I need to make the main table have a field for the desired fields and get rid of the query all together.

If this is the case how do I use the left and mid functions in a table?

 

[LarryDeLaruelle]

Krash:

In the main query, include the full fields values for CardType and MerchantID.

This will allow you to make the necessary links in your second query to those tables.
Larry De Laruelle
larry1de@yahoo.com

 

[Krash878]

The full fields value?
Can you please explain what I would do to achieve this?

 

[LarryDeLaruelle]

Krash:

If I read your initial post correctly, you have created a query from the main table (tblChargeBack) and in that query have included a part of two fields: CardType and MerchantID.

I am assuming that CardType is the Primary Key for the tblCBCardType and foreign key in tblChargeBack and MerchantID is the Primary Key for the table tblCBCodes and foreign key in tblChargeBack!?

You are now trying to construct a second query based on the first and want to include fields from tblCBCodes and tblCardType.

Because the first query does not contain the full foreign key fields to link to tblCBCodes and tblCardType, you cannot make the necessary link.

Is this correct so far?

If so, there are two solutions, I think.

Add tblCardType and tblCBCodes to the original query, link on the foreign keys and add the definitions to the query structure.

If, for any reason, this is not practical, in the original query include the full foreign keys for tblCardType and tblCBCodes as well as your truncated versions. You can then create a second query to include the original query and the two tables since the foreign key fields will now be present in the original query and, thus, allow for linking.

Does this address your question, or am I totally off the mark?
Larry De Laruelle
larry1de@yahoo.com

 

[Krash878]

Yes I believe we are on the same brain wave. I do not know the term "foreign key". In the query there are two feilds that use the mid and left function. Niether one is reflected in the main table at all. All of my Relationships work as long as they are in the table level. But when I begin to use Relationships based off of the left and mid feilds it does not work.

I can send the Database to you and have you look at it and then post here. I use Access 2000.

If you can describe how I would set up foreign keys then that may work too
Either way
Thanks
Krash

 

[LarryDeLaruelle]

Krash:

We're making progress. Foreign key is a term used to describe a field used in one table to provide a link to another table.

Let's see if I have this right. In your case, CBRSN is the primary key in the table tblCBCodes (?). You also have a field called CBRSN in tblChargeBack (?); this would be considered the foreign key and is what allows you to link tblChargeBack to tblCBCodes for the descriptor information.

The same situation exists with tblCardType and MerchantID (?); MerchantID is the Primary Key in tblCardType; tblChargeBack has a field called MerchantID (?).

If that is the case, what you want to do is to add tblCardType and tblCBCodes to the query. If relationships have been set, the links will be created automatically. Just add the descriptor fields you need and you should be done.

Let me know how it goes.
Larry De Laruelle
larry1de@yahoo.com

 

[Krash878]

ok
I will see if I can describe everything.
Database Contains:
tblChargeback
tblCardType
tblClient

qryChargeback

Each ones contents:

tblChargeBackate/MerchantID/CCNumber/PostDate/TranDate/TranType/CBRSN/CatCode/SeqNo/Amount

tblClient:ClientID/Name/Address

tblCardType:Code/CardType

qryChargeback Exactly like tblChargeback except between MerchantID and CCNumber is a Feild CardType that use Left Function " CardType: Left([CBRSN],1) " and ClientID which uses mid function
" ClientID: mid([MerchantID],4,3) "

I wish to link the ClientID to tblClient [ClientID]
and
I wish to link the CardType to tblCardType

The merchantID contains the Bank that houses its business in the 4,5,6 digits.

The CBRSN can only begin with a 4 or 5 
4=Visa
5=MasterCard

We do not use any other cardtypes


The next query would give me all the information on the charge back including the Bank Name and address and what type of card it is.

I hope I gave enough information

in the Client Table is 106 Client Banks
in the Merchant table which I am not having any trouble with is 248,026 merchants.

thanks

Krash

 

[LarryDeLaruelle]

Krash:

The picture is getting clearer.

You will need a second query. Include qrqChargeBack, tblClient and tblCardType.

If the system does not make the links automatically, click/drag ClientID from qryChargeBack to ClientID in tblClients; click/drag CardType from qryChargeBack to CardType in tblCardType. This should create the relationship you need.

Add the fields from qryChargeBack, tblClient and tblCardType and you should be good to go (I hope).

Is this getting us closer to a solution?
Larry De Laruelle
larry1de@yahoo.com

 

[Krash878]

ok now I have a problem with my clients Relationship.

I built the query and it works fine with the CardType.

My problem is one that causes many problems in our company.

Each individule bank has a number assigned to it. That number is the primary key.

The ClientID is a number that is assigned by our chargeback department. That number is in tblClient under field CBID.
When trying to make a link with that number it only shows the last record on the list.

For further reference Each department has a different number for each bank.

I think I can make a query for the Chargeback department and link that way or is there something else I can do.

 

[LarryDeLaruelle]

Krash:

You're right, that is a major problem.

Question: is the single returned record for the bank correct based on the ID link?, that is, is the limited number of returns the result of the departmental use of multiple bank IDs.

I think you may be on the right track by setting up a separate query to link the bank info.
Larry De Laruelle
larry1de@yahoo.com

 

[Krash878]

I did set up a second query and now have used 3 queries to get to one goal. Oh well, it works. Thanks for your help.

Krash

 

[LarryDeLaruelle]

Krash:

When you're working with what someone else set up you often have to take the long way around. What counts is that it achieves the desire result.

I'm helping a school corporation develop some reporting capability out of a system designed by the State. I'm sure that there is some logic to the way the database was designed and one of these days I'll figure it out.

Oh well, I'm being well paid for it so I guess a bit more gray in what's left of my hair won't matter.
Larry De Laruelle
larry1de@yahoo.com

 

[Krash878]

I have a newer post on the same area but not hte same topic can you check it out for me

Thanks
Krash