Solved: Joins and what I want them to mean to me.

[Tommy]

I know what I want and what I get will be different.:devil2: I am willing to settle. :rofl:

I say I have experence with databases, but it is simple tables with no joins. I don't know how to get the key of an Autonumber. I don't know how to insert/create/delete a join. This is what I do know I don't know.:rotlaugh:

The attached "database" is a sample for me to understand better, joins. This is a learning thing, when I get my head screwed on right I will revise and recount the loses .... Yeah that bad. I have the relations setup. I don't think they are right, I think the assembly and condition are bassackwards.

What I want to do: (refer to tables)
I want to send a query to conditions, get back the assemblies, with the parts, and parts. In all of the items the will be a field with cost and weight (I left that one out, one thing at a time hehe). I want to total all cost and weights for this condition.

I have a form setup for input, I will have to duplicate this in VB after I figure out what I think I am doing.

Generally speaking I will be asking for conditions only. The condition should have all parts and assemblies included, the assembly will have all the parts included with it.

More answers will result in more questions of course.

[Tommy]

Well that's what I figured.:yes What I want it to mean and what it means is not what I want.

My relationships were not even close. May still not be but this seems to look like about what I want to do. The problem I am now running into is the forms look backwards to me and it's more than likely the way I am looking at them.

The part form works the way I want it to but I have to type in a number in the id fields otherwise I add a record to color finish and gage. Not a problem there (that is about the only place at the moment).

In the qtypart query the query looks right but the form generates backwards.:bug: So I got a real funny feeling it is not correct.:doh:

[OBP]

Tommy, your relationship set up looks pretty good to me, although I am not absolutely certain that you need a seperate Part Quantity table.:thumb

Can't the Quantities go in the Assembly Table?:think:

In the Parts Form the set up is not optimal, you should have the Form Data Source based on the Parts Query only.
The Colour, Gage and Finish should be selected from Combo Boxes based on Queries on their Tables. The Combos should be "Bound" to their respective fields on the Parts Form.
I have reset the Parts Form data source to the Parts Query and created one Combo for colour to show you what I mean.

[Tommy]

Hi OBP :hi:

Thanks for responding.


The Colour, Gage and Finish should be selected from Combo Boxes based on Queries on their Tables. The Combos should be "Bound" to their respective fields on the Parts Form.

I plan on doing this on the VB side. Just exactly like you have it. The only thing is on the join, do I have to specify that in the sql on the insert? or when I do the select and specify the join it will get picked up? Is this what people call SQL block? :rotlaugh:

The way I'm thinking right now (notice I said right now) is I select a condition, it sends me (1 or more) assemblies and any extra (1 or more) parts that are required. When the Assembly query fires it should return the quantities and parts required for this assembly. When this query finishes (It will fire at least 4 times it could fire up 8 times) I generate a price, weight, and quantity.

OK I'm thinking that the Assembly shouldn't have a join on the parts. I should have a select into to create a table on the fly. That way an assembly can be a group of parts that get selected on color, gage and finish instead of having a different assembly for each. Then I can complain (I love it) when there is a part required without a price.


Can't the Quantities go in the Assembly Table?:think:


See I was being short sighted and narrow minded again.:dunno I could select with the Name instead of just focusing on the primary key. I was thinking that I need to select just the primary key, I think I tried to make it more difficult than it need to be, in other words I am thinking too much.:rofl:

Thank you so much for your input. I will look into it again tonight and rework it. It will probably be a day or so before I can post something worth looking at.

[OBP]

Tommy, the query should for bringing back the assembly details should only run once and give you all the data in one go. (assuming that the relationships and Query are correctly created)
Did you understand how the "Colour Combo" works?

[Tommy]

Did you understand how the "Colour Combo" works?
The way you have set it up? Yes
What actually it did? No
Can I duplicate it for Gage and Finish? Yes

Setting the index number to the color, I understand what it did, I just don't know "how" it did it. Did it use a query? or did the number just get placed in the join during an insert behind the scene? Or did the Part.ColorID get set and the database took care of everything else?

I know the combo will add colors. I don't want that in this particular case but I'm not worried about it because I will need to for something else and all I have to do is change the curser type.

Now the question I have is - If I have a recordset, I set/change the ColorID in the Part, do I need to actually create a join or will the database do it? (I don't know if the client will have Access) I will be using ADO/Jet which is what Access uses in the background. Or is the actual join used in the select query only?

I don't mean to ask so many questions in one thread. I can start a whole bunch of them :) but I have to figure out what to ask.:wot

[Tommy]

After futher review. That was slick.:thumb You selected into the ID :) based off what was returned from the dropdown selection. There is a whole new world when you stretch the form out. :rotlaugh:

[Tommy]

Ok I think I got the Assemblies. At this level the parts are defined and there will be no changes to the parts, just quantities, and adding/deleteing parts.

This seems to have gotten easier. : pray2: It is starting to make sense :beerchug: