Relationship must be on the same number...

[hunterfan48]

of fields with the same data types.

I am now getting this error. I've posted two screen shots of what I'm working on. The 1st picture (untitled) is of me working on my (backup database) to remove the lookup fields in my table.

I have done that and now I'm trying to recreate the previous relationship I had.


On the second screen shot (current) it shows what I am currently working with and how it is set up in my relationships.

I am trying to create a relationship (from the 1st picture) that will create a one-to-many from the unique key (autonumber) ID in tbltransactiontype and relate that to the (text) field transaction type in tbltransactions.


Please advise...thank you. FORGOT SCREENSHOTS...ADDED IN 2ND POST BELOW!

 

[hunterfan48]

sorry...forgot the attachments!

 

[CBrighton]

Presumably both those fields are the same datatype (numberic I assume)?

 

[hunterfan48]

nope...it is a autonumber field to a text field.

In my current database that I'm trying to change, it is set up as an autonumber field to a number field.

Now it will be an autonumber field to a text field.

 

[CBrighton]

The error message tells you what you need to know:

Relationship must be on the same number of fields with the same data types.

Number and text are different types.

If you intend to link them then the contents of the text fields must only be numbers anyway, right? Change the field to numeric (take a backup of the table first in case it deletes the current values of that field) and it should work. If the field currently contains text (as in non-numeric characters, a-z, A-Z, symbols, whitespace, etc) then you have bigger problems as the link will never work.

 

[hunterfan48]

The error message tells you what you need to know:

Relationship must be on the same number of fields with the same data types.

Why does it say the same number of fields when I have different relationships set up between tables with a different amount of fields in each table?? It doesn't give me an error message for those...

Number and text are different types.


If you intend to link them then the contents of the text fields must only be numbers anyway, right? No Change the field to numeric (take a backup of the table first in case it deletes the current values of that field) and it should work. If the field currently contains text (as in non-numeric characters, a-z, A-Z, symbols, whitespace, etc) then you have bigger problems as the link will never work.
See...here's the issue though. I originally had this field as a lookup field. From what I've read on here, I should NOT HAVE any lookup fields in my tables. So, I take them out and make them all textboxes. Now though, I'm left trying to make a relationship that looks impossible.

Would it be better to have this field in my table be a lookup in this scenario?.

I could do a link, but it would be without referential integrity or update cascading fields. Would you ever recommend doing this?

Thanks...

 

[CBrighton]

Can you re-post the screenshots in decent quality?

The ones in this thread are so blurry I can't even see which tables you are trying to link, and without knowing that it's hard to know what outcome to aim for.

 

[hunterfan48]

Can you re-post the screenshots in decent quality?

The ones in this thread are so blurry I can't even see which tables you are trying to link, and without knowing that it's hard to know what outcome to aim for.

K...but I'll have to wait till I get home. Thanks again for helping out...

 

[hunterfan48]

I know it's small but it's a lil bit clearer...hopefully it helps. Btw...what's an indeterminate relationship type?? Good/Bad??

 

[hunterfan48]

So here's what I did...not sure if it's the best route but it seemed to work. In my table [tblTransactionType], I have the following three fields.

ID
Type
Income/Expense

I got thinking about what my Primary Key should be. Well...if one of my types for the field (type) is "card sale," I will never have another type that exactly matches this. It is unique in it's own self I would assume correctly right??

So with that being said, I switched my PK from ID (which is a number data type) to TYPE (which is a text) that way I can create the one to many relationship I want to because their data types between the two tables match up.

Am I missing something in not having ID as my PK or is what I changed fine because each type is unique in it's own way...it doesn't need an ID to tell us that right???

 

[CBrighton]

It may not index as quick on a text field, not sure.

But if ti works as you have it, then all good.

I can't see the new screenies either, likely hosted on a site which my business signon can't access (of which there are many!). But if you are happy then no need to report (although if you do, attach them instead of hosting them somewhere please).

 

[hunterfan48]

On the topic of indexing fields, what's the benefit of that? Why should I? I don't remember paying much attention to that when I set my tables up...not because I thought I shouldn't, I just didn't know that I should or not.

 

[CBrighton]

An indexed field is quicker to search on that field. Good for PK / FK fields or common searches such as employee ID or name. But if you index too many fields then it negates the purpose, so use sparingly.

 

[hunterfan48]

Why would it negate the purpose? Wouldn't it just speed up searching in all fields?? What would be the downfall to it?

thanks

 

[CBrighton]

Imagine your records are a big group of people.

You can tell them that when you shout "Surname" they should line up in order of Surname and they will remember that order and be able to do it quickly.

Now tell them to memorise the order to stand in for every single attribute that they have, do you think it will be as efficient?

Also, each index takes up harddrive space and increases the time required to save / edit a record on a table with indexes.

Take a look here for more info on indexes: http://www.brainbell.com/tutorials/ms-office/Access_2003/Indexing_A_Field.htm

 

[hunterfan48]

Dude...that was an awesome explanation. Thank you!