Three fields with 2 one-to-many relationships

[hunterfan48]

UPDATE: I think this is working now. I'm sorry for the confusion! Can I only make changes to his query in SQL view?? I'd like to add more fields to show up from my inventory table.

ok...I got it now. I apologize for my mistake. I had my field names entered wrong. transactionID is actually transaction ID and cardID is actually just ID.

I know...bad naming and I need to fix that. But, now atleast this query runs. However, it only returns 1,094 cards. I want it to return all possible cards which would be 3,106. And it should actually be more because if a card has both a buying and selling transaction, then it should repeat that card ID in this query right??

Thanks

In total agreement with all that has been said by all those that are trying to help.
Now coming to what you want & perhaps get over with, just check if below gives some guidelines :

tblTransactions
TransactionID - PK
CustomerID
Buy/Sell

tblInventory
CardID - PK
BuyingTransactionID
SellingTransactionID

Main Form based on tblTransactions
frmtblTransactions

Query for sub-form
qryAllCardsAllTransactionsID

SELECT 
    tblTransactions.TransactionID, 
    tblTransactions.[Buy/Sell], 
    tblInventory.BuyingTransactionID, 
    tblInventory.SellingTransactionID, 
    tblInventory.CardID
FROM 
    tblTransactions 
    INNER JOIN 
    tblInventory 
    ON 
    (tblTransactions.TransactionID=tblInventory.SellingTransactionID) 
    Or 
    (tblTransactions.TransactionID=tblInventory.BuyingTransactionID);

Make a sub-form frmqryAllCardsAllTransactionsID based on above query.
Incorporate it in the Main Form tblTransactions
where the field TransactionID from the Main Form is linked to the TransactionID field in sub-form.

Thanks

 

[RainLover]

I did post my updated relationships on page 1 of this thread.

A lot of water has gone under the bridge since then.

I was hoping to see what it is now.

 

[recyan]

However, it only returns 1,094 cards. I want it to return all possible cards which would be 3,106.

You will have to check that. Just take a TransactionID in the tblTransactions & then check out how many records it has in the Cards Table.

And it should actually be more because if a card has both a buying and selling transaction, then it should repeat that card ID in this query right??

Depends on what you are looking at.
If you are looking at the results of the query, that has been posted, then, Yes.

If you are looking at the subform, then No, because the sub-form will display only those Cards which are associated with a particular TransactionID (the one which is selected in the Main Form). The TransactionID in turn is either a Buy Type or a Sell Type, not both.

However, in case you are looking at both the TransactionID's (i.e. Buying & Selling) in the sub-form ( the query results can display both),
then
For a particular TransactionID selected in the Main Form,
You should be able to see all Cards associated with that TransactionID in the sub-form.
Also you should be able to see the other TransactionID for that Card ( In case TransactionID selected in Main Form is Sell Type, then you should also be able to see the Buying TransactionID for a particular Card in the Sub-form if it exists, else you will see an empty Buying TransactionID field for that Card - This statement is valid if you are displaying both - the Buying & Selling TransactionID in the Sub-form along with the CardID).

PS : Agree with Rainlover. An updated Relatioships upload will perhaps be useful, bcos while solving things, we might have made changes unknowingly.

Thanks

 

[RainLover]

recyan

For some reason the Hunter does not want to answer directly the questions we ask.

He maybe getting help from another site as well. Not as though that is a problem but he is making it hard for us to help properly as we are making too many assumptions.

 

[DavidAtWork]

"It wants me to put a parameter value in?
The parameter request I suspect is coming from the field that appears to be named [buy/sell] in the Select statement above
"SELECT
tblTransactions.TransactionID,
tblTransactions.[Buy/Sell],
tblInventory.BuyingTransactionID,
tblInventory.SellingTransactionID,
tblInventory.CardID"
You may need to rename this field

Also in your "one card or record might have to be repeated twice in this query."
Surely this can't happen because a transaction can only be a BUY transaction or a SELL transaction, it can't be both, so a single card can only appear once in any transaction.
Incidentally in my last post I was only using generic kind of names for fields and forms, you need to substitute the correct field and form names
David

 

[hunterfan48]

A lot of water has gone under the bridge since then.

I was hoping to see what it is now.

They are still the same...nothing has changed.

 

[hunterfan48]

Guys,
I'm sorry. I didn't receive any emails letting me know of your recent posts. I'm not getting help from another site and you guys have been all extremely helpful and I'm grateful for that.

The SQL is working. The reason it was giving me a parameter value was because when I double checked my field names I found out I had two of them entered wrong! Sorry for confusion! However, I was able to put this query to work as a subform to my main form and it is working just as I had planned.

Thanks for all the help and I'll answer your questions soon.

Thanks,
Brady

 

[hunterfan48]

You will have to check that. Just take a TransactionID in the tblTransactions & then check out how many records it has in the Cards Table.
It appears to match up. I'll keep checking in case of any discrepancies.

Depends on what you are looking at.
If you are looking at the results of the query, that has been posted, then, Yes.

If you are looking at the subform, then No, because the sub-form will display only those Cards which are associated with a particular TransactionID (the one which is selected in the Main Form). The TransactionID in turn is either a Buy Type or a Sell Type, not both.

However, in case you are looking at both the TransactionID's (i.e. Buying & Selling) in the sub-form ( the query results can display both),
then
For a particular TransactionID selected in the Main Form,
You should be able to see all Cards associated with that TransactionID in the sub-form.
Also you should be able to see the other TransactionID for that Card ( In case TransactionID selected in Main Form is Sell Type, then you should also be able to see the Buying TransactionID for a particular Card in the Sub-form if it exists, else you will see an empty Buying TransactionID field for that Card - This statement is valid if you are displaying both - the Buying & Selling TransactionID in the Sub-form along with the CardID).

PS : Agree with Rainlover. An updated Relatioships upload will perhaps be useful, bcos while solving things, we might have made changes unknowingly.

Thanks

I'm using this query as a subform to match up with my main form. I have this subform on a tab. It does exactly what I want it to. When the transaction ID changes, the subform changes to match up that ID value from the main form to match up with any cards that has that same ID in either the buying or selling ID.

Thanks again for all the help.