combining two queries to create one query

[hunterfan48]

I have two queries: one has numerous fields detailing the sports card I sold. The other query has a field that has a calculation within the 'net price' field that gives me the the total sale price for that card. For both of those queries, they both have the exact same five records.

When I show both tables in creating a new query, there is no line connecting the tables so I believe there is no relationship. However, when I add all the fields I want from query 1 and add all the only one field I need from the other query, it will give me 25 records instead of 5. In essence, it creates an additional 5 records for every one record.

Why does it do this and how do I get it to stop doing this and only show the 5 records?

thanks

 

[SOS]

That is called a Cartesian Product and happens when you don't link the tables together. They should be linked by at least one common field (usually the Primary Key of one table to the Foreign Key in the other).

 

[hunterfan48]

ok, yea I remember one guy mentioning that before. Only problem though is that these are queries. The net price field only exists in a query...it is a calculation basesd off values from another table. Is there anyway I can work around this?

 

[Brianwarnock]

Join the queries on a related field.

Brian

 

[SOS]

ok, yea I remember one guy mentioning that before. Only problem though is that these are queries. The net price field only exists in a query...it is a calculation basesd off values from another table. Is there anyway I can work around this?

I know you've posted your database before (sorry to ask again) but if you can post what you have and then tell us which queries you are talking about we might be able to make some suggestions.

 

[hunterfan48]

ok great! thanks so much...

I talked with my professor about all these questions I had and he helped me atleast get a working solution for right now.

If you check out Form 1 and Card transactions form, it now works except I still can't enter a new transaction ID in Form 1 without it already being present. So, I just enter da transaction in my transaction form and then go to form 1 to enter the rest of the cards that are related to that transaction.

It works fine for a novice like me, he said I would need to use visual basic in order to accomplish what I'm looking for.

Please check out Query 1. this would help me in related queries as well...

thanks so much!

Again, any tips or comments on how to improve my database are always welcome!

 

[hunterfan48]

Brian,
Does that mean those queries need one field that is exactly the same for both of them?
thanks

 

[SOS]

Check out the revised Query1. You were missing the linking table (Transaction ID).

 

[hunterfan48]

ok thanks a lot man!

 

[hunterfan48]

so after working around wit it, I found that adding the transaction ID table in my query design and drawing relationships between fields didn't mess with my table relationships.

that is correct right whenever I'm workin in a query?

thanks again for the help...

 

[SOS]

It doesn't "Mess with your relationships" because it is part of your relationships and you need to tie the tables together if you are wanting data from both of the tables.

 

[hunterfan48]

Could you help me with this query? What do I need to do fo fix this issue I'm experiencing again??

 

[vbaInet]

Seriously hunterfan48, you've asked this same question in two other threads, one of which is an old thread which you resurrected like this one.

I would advise you to desist from this type of behaviour. It's frowned upon in forums. In the meantime I'm forced to highlight this to the site Mods and Admins.

Have some patience on your original thread.

http://www.access-programmers.co.uk/forums/showthread.php?t=220719

 

[hunterfan48]

Forced to??? No big deal...I didn't mean to offend anybody. My bad...I wont do it again. What's the big deal now??? It's over

 

[vbaInet]

Don't worry, it's not going to affect you. It just helps them keep an eye on things. But please let's not repeat this. Thanks!

 

[hunterfan48]

Will do. That was bad taste on my part. Warning well received. Thanks