Field relating two unrelated tables

[hunterfan48]

Yes...and it is saving a great amount of time indeed!

 

[hunterfan48]

Here's something I came across in updating my database.

When I use a form for entering transactions, how will I show the cards that have been linked to that transaction?

With two seperate columns (buying ID and selling ID) there isn't one column to match up with the transaction ID column.

Do I need to create a query to combine all the buying and selling transactions together into one transaction ID?

That's what I tried here but it didn't work. Any tips for getting the Transaction values to be pulled in with the card values shown below??

SELECT tblInventory.ID, Query1.[Transaction ID]
FROM tblInventory LEFT JOIN Query1 ON (tblInventory.SellingTransactionID = Query1.[Transaction ID]) AND (tblInventory.BuyingTransactionID = Query1.[Transaction ID]);

 

[gemma-the-husky]

i don't quite understand

surely - if you have, say a buying transaction, #1234. then you store this ID #1234 in the appropriate place of the cards table, for all the cards purchased on this ID.

do you have a single transaction that includes BOTH buying and selling? Sounds unusual, but still manageable.

you probably then need a union query to bring together the cards bough, and sold, under this reference

 

[hunterfan48]

Nope,
To use your example, #1234 is only a buying transaction. That same number will not be used for any other transaction. That value reflects a single transaction.

I'm trying to use a subform to show all related cards to the transaction field on my main form. When it comes to linking the two though, I can longer just link ID to ID.

It has become Transaction ID to Buying or Selling Transaction ID.

So...I think I need to do a query to show all transaction values stored in numerical order whether it be buying or selling in just one column.

Does this make more sense?
Thanks for your continued help!

 

[hunterfan48]

After working some more on this, I'm still stumped. I'm not sure how I can get a query to show me all cards relating to a specific transaction. I have two fields within my card inventory table for putting in a value from my transactions table.

I just want to be able to put a subform on the form (Enter New Transaction) that will show me all the cards that have that specific transaction. For further explanation, please see the attachments.

I used to be able to do this with my linking table (tblTransactionID) because the transaction ID from Table (Transactions) would match up with the transaction ID from table (TransactionID). However, now there is no such table because it has been replaced with a buying and selling transaction field in my table (Inventory).

I need to find a way to have a query return all card values with a single transaction column so when on the main form, I'm looking at let's say transaction 0023, it will thene show me all related records on the subform with the value 23.

Again, the value 23 would be in either the buying or selling column...it can't be in both because each transaction is unique.

Thanks everyone for the help!

 

[hunterfan48]

Did the screen shots help explain my problem? Judging from the lack of response and my failed attempts, is this impossible??

 

[recyan]

Guess after 26 posts in this thread, my thoughts might be repetitive, but all the same :
1) Each Card is unique.

2) Each Transaction is associated with one & only one customer.

3) The Transaction can either be Buy or Sell only, not both.

4) Each Transaction can be associated with one or many Cards.

5) Was wondering whether there is a possibility of a Card coming back in to the inventory.
If that is the case, then is storing the TransactionID in the card inventory table advisable.

6) If a TransactionID is either a Buy or Sell, then does it mean, all the Cards in that TransactionID can be either Buy or Sell only. There is no possibility of some Cards being Buy & Some being Sell in the same TransactionID.

7) If both 5) & 6) or only 6) above is true, then why not go with a structure like something below ( In case I have missed the discussion against this type of structure, just refer me to the post no).

tblCardsInventory
CardID - PK
CardDetails

tblTransactions
TransactionID - PK
CustomerID - FK
Buy/SellType - Value either Buy or Sell

tblTransactionDetails
TransactionDetailsID - PK
TransactionID - FK
CardID - FK

Now coming to the present scenario at your end, just see whether below gives some guidelines :

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)
WHERE 
	tblTransactions.TransactionID=23;

Thanks

 

[hunterfan48]

Thanks for the response...all of your points EXCEPT #5 are correct.

However, I don't think creating another table is the right solution. I just was advised that I didn't need a third linking table. Based off of what I have now, I just want a query to show me all transactions and their associated cards. So, if card ID (#2304) has both a selling and buying transaction value, it would then be listed twice in the query as two seperate records.

When I enter a new transaction on the form, I have no way of seeing the cards related.

Is there a way to tell Access if I marked transaction #2393 a "Buy" in my (Buy/Sell) field it will use a specific query. But if I mark it a "Sell" it will use a different query??

 

[recyan]

However, I don't think creating another table is the right solution. I just was advised that I didn't need a third linking table.

Ok.

Based off of what I have now, I just want a query to show me all transactions and their associated cards. So, if card ID (#2304) has both a selling and buying transaction value, it would then be listed twice in the query as two seperate records.

When I enter a new transaction on the form, I have no way of seeing the cards related.

Have you tried the query which I have posted in my previous post with & without the where clause?
If yes and it did not work, what happened?

Is there a way to tell Access if I marked transaction #2393 a "Buy" in my (Buy/Sell) field it will use a specific query. But if I mark it a "Sell" it will use a different query??

In this case, the TransactionID itself defines Buy or Sell either by its Buy/Sell value in tblTransactions or by the TransactionID field value in BuyTransactionID or SellTransactionID in the tblinventory.

Pls note : I am only looking at things from Query point of view & not Form point of view.

Thanks

 

[hunterfan48]

I'm working on the query right now and can't get it to bring up anything...I'll post some more details in a second.

Ok...so here's how it goes.

After entering the SQL and clicking 'Run,' it says...

Enter parameter value for tblTransactions.Transaction ID

I then click enter

Enter parameter value for tlbInventory.CardID

The query returns no results. From which, I then click design view and get the following message.

"Microsoft Office Access can't represent the join expresssion (tblTransactions.TransactionID=tblInventory.SellingTransactionID) Or (tblTransactions.TransactionID=tblInventory.BuyingTransactionID) in Design View."

*One more fields may have been deleted or renamed.
*The name of one or more fields or tables specified in the join expression may be misspelled.
*The join may use an operator that isn't supported in Design View, such as > or <.

Thanks for taking the time to help out!

 

[recyan]

Hi,

The tables :

tblInventory

CardID    BuyingTransactionID    SellingTransactionID
1    1    3
2    2    4
3    2    4
4    1    3
5    2    3
6    2    4
7        
8

tblTransactions

TransactionID    CustomerID    Buy/Sell
1    10    Buy
2    11    Buy
3    12    Sell
4    13    Sell

The query, posted earlier :

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)
WHERE 
    tblTransactions.TransactionID=3;

should give results something like below :

TransactionID    Buy/Sell    BuyingTransactionID    SellingTransactionID    CardID
3    Sell    1    3    1
3    Sell    1    3    4
3    Sell    2    3    5

Just check if above is in line with the direction in which you want to go.

PS : The query cannot be edited in the Design Window (bcos of the join used). It has to be done in the SQL window itself. If you try to open it in Design window, it will give those error messages which you are seeing & then ask you if you want to save & if you say Yes, the original query is gone.

Thanks

 

[hunterfan48]

It still asks me to enter parameter values and doesn't return any records.

 

[recyan]

1) Is the details I posted in the previous post in line with what you are looking for?

It still asks me to enter parameter values and doesn't return any records.

2) The query is expecting you to supply the TransactionID, which it is not getting. Hence the message. Think you need to check your WHERE clause.

Thanks

 

[hunterfan48]

Yea...it would work, but I would like it to return all the transactions...I don't want to put in just one.

I'm thinking about starting over and figuring out a new way to display the data...aftere this much headache, I think there must be a better way to show it on a form.

I think part of my problem is that I want to show so many different things on one form. I should seperate them out and use different forms to help display my data.

Is this smart?

 

[recyan]

Yea...it would work,
but I would like it to return all the transactions...
I don't want to put in just one.

The query will display all the CardNos associated with a particular TransactionID.
At the same time it will display the other TransactionID's associated with the Cards.
For eg. if we query for TransactionID "3", which is specifically a Sell Type Transaction, the query will display all the cards associated with TransactionID "3" & also display the Buy TransactionID's if any, against each of the cards & vice a versa.

Was not able to understand

but I would like it to return all the transactions...
I don't want to put in just one.

Can you just post based on the earlier dummy tables data posted by me, what kind of results you are looking for.

I'm thinking about starting over and figuring out a new way to display the data...
aftere this much headache,

Many a times, the moving away & stopping the thinking about what we are doing throws up real good ideas.

I think there must be a better way to show it on a form.

I think part of my problem is that
I want to show so many different things on one form.
I should seperate them out and use different forms to help display my data.


Is this smart?

Sorry, Really can't help you with the Forms & Reports part, as am totally out of touch with the same.

A suggestion :
Start a fresh thread, bcos many times people tend to ignore a thread which has too many replies, as they feel they will have to read from start to end to understand the problem & then reply (Also I think, the thread has moved on from the original problem to a different area).

Thanks

 

[hunterfan48]

I agree and will follow your advice. It is now not so much relating two unrelated tables because that problem has been solved, but now I need to figure out how to correctly display my data.

Thanks for all your help...your suggestions are very helpful!