How to assign existings items instead of inputing new ones

[jaryszek]

Hi Guys,



sample database is from here:

https://www.accessforums.net/showthread.php?t=73247


I have a main form:



Here i can input new data.

I am choosing CatNAme, what is a FoodName for him and on subform i am choosing Color of Bowl and Adding Cat Toy for his Kitten.

This is ok when i want to input new data.
But what if i would get new Kitten data and want to input it separately?

So i want to first Add Kitten and CatToys for them and later assign him to Parent Cat? Should i create new form?
Or somehow there is possibility to use existing one?

Please help,
Best,
Jacek

[ranman256]

1 cat only needs 1 subForm for cat toys.
the do not need 1 for bowls. 1 cat only needs 1 bowl.
or
just put EVERYTHING in the one subForm....
catID, itemType, Item,color
2,toy,ball,blue
2, bowl, food bowl,red.
etc...

[jaryszek]

Ranman256

thank you.

I do not know if you read my post from link. To have this working i have to have 1 field for bowl as separate subform. This is many to many relationship and there is not other option.
If you can set up this technically - please send an example based on my attachment.

just put EVERYTHING in the one subForm....
catID, itemType, Item,color
2,toy,ball,blue
2, bowl, food bowl,red.
etc...

Question is how to input first Kitten and their CatToys and secondly bind it with PArent Cat...
Best,
Jacek

[June7]

Kittens become cats. This means children become parents.

Should be one table for all cats with fields for motherID and fatherID (and the source for these is the catID). This is like a family tree or employee/supervisor or manufacturing/assembly db. The table is recursive (it has relationship to itself). Probably most difficult type to understand and design.

[jaryszek]

Hi,

thank you June7.

You mean to have something like:



So example:

CatsID = 1; CatName = Kacper; ParentID = Null
CatsID = 2; CatName = Kid1; ParentID = 1

There is no possibility to build recirsive query in Access but how to handle it later? Build a form with subform for this?

Best,
Jacek

[June7]

You can build a query where a table links to itself. It means including the table in the query twice (or more). Yes, Access does not handle recursive query nicely. Count myself lucky I never had to build. Search web and find examples, maybe this will be helpful https://access-templates.com/tag/mic...+database.html

[Minty]

I did mention the Cat / Kitten relationship in one of the previous postings.

Jacek, you have opened a number of threads both here and at access users( https://www.access-programmers.co.uk...d.php?t=300877) mostly regarding hypothetical scenarios.

What I think you really need is to show us a real world example of the data you are trying to store, and the expected output.
I know you say you can't show us this but without the actual business model (all the facts) and the expected results, we are all guessing at the real nature of your queries and questions.

I would be surprised if your real world situation (or something very similar) hasn't already been tackled by someone here.
We don't need to see personal data, just all the salient facts.

[jaryszek]

Hi Minty,

yes you wrote about this recursive tables.

I created another topic on another forum to find out more about creating relationships rules - not a model.
People there explained very well all my issues. This is not connected topic.
Furthermore, this hypothetical issues are very useful for me - i know how to build model and use best practices.

We talked about model in separate topic. And i did this model Topic was closed...

Thank you,
Jacek

[Minty]

Okay, if you have sorted your issue then all is good.