Dataset structure

[The One Who Was]

Greetings:

 

I've ran across a problem when working on a practice application for myself.

 

There are 6 tables. Group, Category, Type, Manufacturer, Products, and Misc

 

The Group table contains the fields GrpNumber and Description. Neither GroupNumber or Description appear to have completely unique values.

 

My problem occures when I try to fill my dataset. I have a relation created between Products and Group which is linked by GroupNumber and products as the parent.

 

This causes a problem since both table's GroupNumber fields violate the unique constraint. I am curious if it is possible to have a "Many to Many" relation that doesn't depend on unique key values? This would allow me to to look up the Group Description useing the relation.

 

Any help , suggestions or advice is greatly appreciated.

[hog]

Not sure I follow you but if you add an AutoNumber field in the Group table as the unique key then use this field to link to products

[The One Who Was]

Hog:

 

My apologies for not explaining the situation all that well.

 

If I add a auto number field to the group table, will I require a field in the products table that has a corroponding value as the new field in the Group table? so that the relation can work? Or am I misunderstanding the picture on relations?

 

Please note that these tables already have been populated with values. This is just an application that will allow me to look at the products a little easier without updating, inserting, or deleting records.

[hog]

Yes, the AutoNumber value in the Group table will be needed in the Products table to create the link.

 

Adding the AutoNumber to Groups will be no problem, but I'd need to see a sample of data to see the link you have at the moment between the too, long day and all that and another hour & 10 to go:(

 

post a couple of records from each table so we get a better picture:)

[The One Who Was]

Hog:

 

Hope you manage to get thru the 10 hours =P

 

I did just manage to find a way to complete this. The solution was the table key. I found I could add more than 1 field in the dataset key. Using both Grp and Description fields in the key allowed my to fill the dataset properly.

 

Thanks for the assistance. I greatly appreciate the time you took to read my rather confusing posts =)

[hog]

:) no probs....

[The One Who Was]

Greetings again:

 

I thought I had a solution but I still haven't fully figured this whole thing out yet...

 

I've attached a sample database. Please have a look at the data, relations, and the example query.

 

I'd like to have a dataset display data like example query does. However I cannot seem to do thing since I cannot create the same relations due to constraint errors (Unique and non-null).

 

I'm open to any suggestions or advice.

 

Thank you in advance for your time.

test.zip

[Nerseus]

I think you want a join between Products and Group, with the parent being Group and the child being Products (a product contains the Group code). Make sure to add Primary (unique) key to the group table first. This is all done in the dataset, btw. I didn't look at your database too closely, but it seems like this is what you want.

 

-Nerseus

[The One Who Was]

Nerseus:

 

I do agree. That is the usual way I do this, but this situation seems different. Maybe my mind is wrapped around this wrong.

 

I'd like to navigate via the products table. If I create the key in the Group table, making it the parent of the relation... it seems to work backwards.

 

I'd like to display the Group description based on the product, not the other way around. . . or another way of putting it... I'd like to display all the records from the products table, and the 1 corrosponding Group record according to the selected record in the products table.

 

I could probably do this with some confusing dataviews but if I could get this relation to work it'd be much simpler

 

I hope you can piece it out and make some sence of it, and possibly throw me a suggestion.

Edited January 28, 2004 by The One Who Was