complicated and confused??? lol

[hunterfan48]

ok so here's the deal

I'm tryin to make a query from 2 different tables. everythin works...it's great!

now, I want to add a specific field from another query to filter my query I jus created even more. so I click 'show table' and it I add that specific query that has the field I want to use as part of my query. But, then it completely throws off my created query.

This table that I just added has no relationships or anything with the 2 tables I'm using for my current query so why does the fact when I even show it, it automatically messes up my filtered records from the query. Simply put, it muliplies the current records in my created query by the amount of records in that table I jus added...but keep in mind that I haven't even added a field from that new table to my query.

I asked my professor today and he said he didn't understand why it did that either...my tables are good and my relationships, so there should be no reason for this to happen.

Funny side note tho...when I do click on the 'totals' tab while modifying my created query, it puts everything back to normal??

weird huh? plz help if u can! thanks!

 

[DCrake]

May help if we could look at the tables you are using

 

[gemma-the-husky]

thats what it does

if you dont establish a join, you get a (cartesian) cross-product join

each item in table a is cross-matched with each item in table b.

================

so what do you think should happen? What relationship does the unconnected data have to the normal base query?

ie. how can you use something from the third table if it is not related to either of the previous tables?

 

[hunterfan48]

I'm not sure how to post my tables on here, but I'd be willing if someone would let me know how to do that?

So what does a cartesian cross-product join mean? Can I delete it that way it don't interfere? How come it does it on certain queries and not others?

thanks

 

[gemma-the-husky]

lets say you have a query with 20 items

now you add another table with 10 items, but dont link it to the exsiting queries

=========
it means that for each of the 10 items in the new table, it can be paired with all of the 20 items already existing

hence 10 x 20 = 200 items - this is called a cartesian join.

==========
the question is - if the new table isnt related to any of the items in the existing query, how could/did you expect it to modfiy the items you had alrerady obtained by the existing query ?

 

[hunterfan48]

by link, do you mean make a relationship between the two in my relationships tab?

that is exactly what it's doin...thanks for explaining that!

I'm sorry I'd have to look again and when I find out what I was trying to do I'll post it, but is there anyway to get rid of that cartisan join in case I need to use another table?

ur very helpful...thanks again

 

[hunterfan48]

btw...in addition the questions above, why when I click 'totals' in my query design page, it then runs the query normally no matter how many tables I have in there??

 

[gemma-the-husky]

make a link between the tables in the query window

if the link is defined in relationships, access will do this automatically. if two fields are the same name, access will do it automatically, but may get it wrong

=====
to repeat - there is no point bringing in another table, unless it links to another table.

=====
totals probably does that becasue everything is a groupby

you need to change one of the groupbys to a domain function, like sum, or count.

you will then find you have too many groupbys - ie some fields that you arent bopthered about. delete those from the query

just have a play with it, you will see how it works.

 

[hunterfan48]

ok...will do. I'm not sure how all this is goin down yet lol but I'll guess I figure out more as I go.

thanks for da help...