Three fields with 2 one-to-many relationships

[hunterfan48]

Here's where I'm at guys.

Table 1:
(Transaction ID)

Table 2:
(Card ID)
(Buying ID)
(Selling ID)

I have a one to many relationship from from Transaction ID to Buying ID as well as a one to many relationship from Transaction ID to Selling ID.

I have a form based off table 1. So, I would like to add a subform that relates every card ID to that specific transaction ID.

Each transaction ID is either a buying ID or selling ID...it can't be both.

How can I have the transaction ID value query my table 2 to match the same transaction ID value in either the (buying selling ID) field or the (selling ID) field?


Thanks,

 

[RainLover]

Your Table design could be better. I don't know if this is totally correct but it is a step in the right direction.

tblTransactions

TransactionID Autonumber
OtherInformation


TblCards
CardID Autonumber
TransactionID Long as Foregin Key to tblTransatctions
Buying Could be Text or whatever you need
Selling Could be Text or whatever you need

If this is no good then please post your structure as per my example.

Include a better description.

 

[hunterfan48]

I apologize for the delay, but I do appreciate the response. I was advised to go with this structure and I do believe it to be for the better. Here is what we're looking at.

Please see attached screen shots.

Thanks!

 

[RainLover]

Let's start with your Form. It looks good. Crips and Clear. I think users will have no difficulties with eye strain or anything like that.

Next is Reserved Words like "Date", "Year". You can get away with using them but there will be problems sometimes.
http://www.allenbrowne.com/AppIssueBadWord.html This link will help you find other reserved words.

Naming Conventions.

in tblInventory you use "ID". You would be better off using InventoryID or InventoryPK. (PK for Primary Key. Then you could use FK for Foregin Key). If you choose ID as most do then also name your Field in the Foregin Table the same. You have done this in some places but are not consistant.

No special Characters or spaces. Like / \ * & etc. Not a must but again much easier down the track.

Memo Fields are subject to corruption. So I would advise putting them in a Table of their own, a One to One Relationship.

Personally I would separate Purchases and Sales into Tables of their own.

You have Buy/Sell in tblTransactions as well as TransactionType. Doesn't Transaction Type cover Buy/Sell so there is no need to have Buy/Sell.

tblTransactions lists what you are buying under Description. (I think) If this is the case I would have a separate table for Product Details and relate this back to tblTransactions.

tblInventory has something similar. Player Name. Again I would use a separate Table for Players. Team should also be in a separate Table and related back to Player or perhaps tblInventory but I think tblPlayers would be correct.

You need to look at repeating Data and place these in separate Tables. These could be Sport, Manufacturer, Team, Location, Image, MarketPlace, City, State, Zip, County.

It looks like I have destroyed your design. I did not intend to do so. I was simply following Normalisation Rules.

Anyway the decision is yours. Please let me know what you have decided.

 

[RainLover]

I just noticed Player Name as one Field.

This should be PlayerFirstName and PlayerLastName.

Two separate Fields.

If you want to display these as one Field you can join them together, but please store them as separate fields.

 

[DavidAtWork]

As RL states above you need to give more thought to your table design.
If a single card can only have one buyID and sellID then it would be ok to have those fields in your card table that would link to the transaction.
If a transaction can only be one type either Buy or Sell, then you should be able to have a form displaying your transactions with a sub form underneath displaying all cards involved in each transaction. The sub form will operate on a query:
SELECT tblCards.* FROM tblCards WHERE ((tblCards.BuyID = forms!frmTransactions.TransactionID) OR ((tblCards.SellID = forms!frmTransactions.TransactionID))
You can either build a query from the query builder in the properties of the from record source and use 2 lines in the criteria (one for each BuyID , SellID) or in the OnCurrent event you can set Me.RecordSource = the above SELECT statement
David

 

[hunterfan48]

As RL states above you need to give more thought to your table design.
If a single card can only have one buyID and sellID then it would be ok to have those fields in your card table that would link to the transaction.
If a transaction can only be one type either Buy or Sell, then you should be able to have a form displaying your transactions with a sub form underneath displaying all cards involved in each transaction. The sub form will operate on a query:
SELECT tblCards.* FROM tblCards WHERE ((tblCards.BuyID = forms!frmTransactions.TransactionID) OR ((tblCards.SellID = forms!frmTransactions.TransactionID))
You can either build a query from the query builder in the properties of the from record source and use 2 lines in the criteria (one for each BuyID , SellID) or in the OnCurrent event you can set Me.RecordSource = the above SELECT statement
David

This SQL doesn't use any of the fields I actually have in my table. I tried inputting it, but it doesn't work. I think you understand what I'm trying to do. I simply want each card ID to popup on a subform that relates to the main form's transaction ID number.

Thanks for the suggestions. Please explain further how to make this happen.

Thanks!

Btw...RainLover thank you for all of your suggestions. I really appreciate the help and respond to you shortly.

 

[hunterfan48]

Let's start with your Form. It looks good. Crips and Clear. I think users will have no difficulties with eye strain or anything like that.
Thank you...I wasn't sure so I'm glad to hear that it looks good. Btw...I'll be the only USER.
Next is Reserved Words like "Date", "Year". You can get away with using them but there will be problems sometimes.
http://www.allenbrowne.com/AppIssueBadWord.html This link will help you find other reserved words.

Naming Conventions.

in tblInventory you use "ID". You would be better off using InventoryID or InventoryPK. (PK for Primary Key. Then you could use FK for Foregin Key). If you choose ID as most do then also name your Field in the Foregin Table the same. You have done this in some places but are not consistant.

No special Characters or spaces. Like / \ * & etc. Not a must but again much easier down the track.

Memo Fields are subject to corruption. So I would advise putting them in a Table of their own, a One to One Relationship. Maybe I should call it 'Notes' and just have it be a text field. It's only lil extra tidbits of information...

Personally I would separate Purchases and Sales into Tables of their own.
I was advised against and myself don't see fit...I don't need to seperate purchases and sales into two tables. I have queries to do that.
You have Buy/Sell in tblTransactions as well as TransactionType. Doesn't Transaction Type cover Buy/Sell so there is no need to have Buy/Sell.
I'll need to look in to that...been meaning to, but I have some queries I'd have to go back and take a look at.
tblTransactions lists what you are buying under Description. (I think) If this is the case I would have a separate table for Product Details and relate this back to tblTransactions.
Why? What's the point of creating an extra table when I'm all using 'Description' for is to give a brief description of the transaction.
tblInventory has something similar. Player Name. Again I would use a separate Table for Players. Team should also be in a separate Table and related back to Player or perhaps tblInventory but I think tblPlayers would be correct.

I'm just not seeing the need to create extra tables, but then again I'm not an expert. If I would do a players table, you would be correct in relating it to that. Team is in its own seperate table...why should it be related back?

You need to look at repeating Data and place these in separate Tables. These could be Sport, Manufacturer, Team, Location, Image, MarketPlace, City, State, Zip, County.

Not to repeat my question but what would be the benefit to doing this?

It looks like I have destroyed your design. I did not intend to do so. I was simply following Normalisation Rules.

Anyway the decision is yours. Please let me know what you have decided.

You were very kind to take some considerable time to evaluate my screenshots and provide some much needed feedback. Please don't take offense at any of my replies. I'm just simply typing what I'm understanding. I do realize that I'm the new one here trying to learn, asking questions, etc. I will definitely listen to you guys, but in this process I need to ask questions so I will better understand both now and for future use.

I look forward to your replies.

And to save another post,
What's the benefit of breaking down name into two seperate fields (last and first name)? I've gone through 3,000 cards to put into this database just inputting the name all into one. With sports cards, the full name is most important. That's what I'm concerned with so I went with that.

However, if I wanted to, couldn't I run a query that would split the name field into 2 seperate fields and pull the 1st name into the first name column and the 2nd name into the last name column? I swear I've seen something like that done before...

Thanks again for all of your help!

 

[RainLover]

I shall not answer all your questions as some of my statements were made on assumptions and may not apply in your situation.

As I said in my last post the decision is yours. You can go with what you have or improve the design by following Normalising Rules. It can take a lot of time and effort to get the design correct but it is worth the effort.

One thing I can guarantee is that a poorly designed structure will lead to more and more complicated coding to get you where you want to go.

I am not about to write about Normalisation as so many have done this before me. I suggest that you Google it and learn from others. Microsoft have a good article in their Knowledge base that I would recommend to you.

I will however give you one example.

You have "City", "State", "County" "Country" and "Zip Code"

Every time you add a new record you have to complete all of these Fields. Lots of room for Mistakes and a Lot of Typing.

If these were already listed in separate Tables then all you would have to do is to select the "City" then everything else would appear automatically, and you will know that it is correct. But if for some reason the City was spelt wrong you could change it in one place and every single record using that City would be corrected. This is called "Data Intregity" and it is but one Part of Normalisation.

 

[tehNellie]

Just to chip in, I agree with Everything RainLover's said.

And to save another post,
What's the benefit of breaking down name into two seperate fields (last and first name)? I've gone through 3,000 cards to put into this database just inputting the name all into one. With sports cards, the full name is most important. That's what I'm concerned with so I went with that.

However, if I wanted to, couldn't I run a query that would split the name field into 2 seperate fields and pull the 1st name into the first name column and the 2nd name into the last name column? I swear I've seen something like that done before...

Flexibility and accuracy. You have a "Name" Field at the moment, presumably formatted "Firstname" space "Surname". What happens if you want everyone called Smith in your output? You have to use a LIKE clause rather than just WHERE Surname = "Smith" how is that going to deal with people who have surnames that might be firstnames and vice versa? what about people with double barrelled first/surnames? What happens when you want to sort your output alphabetically by name?

"Aaron Zackary" will come before
"Jim Abrams"

"Name" is not a single piece of data, it's two, possibly three so treat it as such.


It might seem like extra work for little benefit right now, but half an hour extra getting the data right at the beginning will save you hours, days, weeks of time messing around and thinking "why didn't I...." later.

 

[RainLover]

tehNellie

Thanks for that.

I did forget to address this part.

 

[hunterfan48]

Ok guys...thanks for the tips. I'll be sure to check further into this to make sure my database is sound.

Back to the original question though, how can I get a query to return all card values that match a specific transaction?

 

[recyan]

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

 

[hunterfan48]

It wants me to put a parameter value in? Let's say I have 100 cards (100 records) in my inventory table.

Well, this query that I want to run, I understand that because I have both a buying and selling field within my inventory table, one card or record might have to be repeated twice in this query.

For example,
Card ID Buy ID Sell ID
3 0004 0005

Obviously to run a query that will show ID as well as a matching transaction value, this card ID would have to be repeated to once show a transaction for 4 (buying) and 5 (selling).

Does this help?

 

[hunterfan48]

You are 100% on with what you are trying to do. Now we only need to get this code to properly work.

 

[recyan]

You are 100% on with what you are trying to do. Now we only need to get this code to properly work.

Hi,
I am confused, with the previous & this reply.
Was the suggestion helpful?

What I have suggested, should ideally display all the cards associated with a particular TransactionID. I assumed that is the way you want things.

In case, I have it wrong, let us know.

Thanks

 

[hunterfan48]

Your thinking is correct in trying to get it to do what I want. But the query isn't working.

It is asking me for a parameter values. How can I just get all cards to be returned?

 

[recyan]

It is asking me for a parameter values. How can I just get all cards to be returned?

Can you post the query that you are using.
What parameter value is it asking for?

If you run the query that I have posted separately initially, it should not ask for any parameter. It will display all the TransactionIDs with all the Cards associated with them.

Thanks

 

[RainLover]

Your Inventory Table should have TransactionID as a Foregin Key.

Then you would have a single Join via TransactionID.

If you wanted a Form and Sub Form situation then Select all the fields from tblTransactions as the Main Form and all the fields from tblInventory as the subForm.

When you add the SubForm to the Main form you will be prompted for a Common Field that joins the two froms together.

If this does not happen have a look at the Properties box for the SubForm. (A SubForm is a Control belonging to the Main Form) Find Link Child and Link Master under the Data Tab. You should select TransactionID for both.

Now when you open the Main Form One only Transaction per record will show. But in the Sub Form it will show all the matching Records.

As you scroll through the Records on the Main Form you will see the sub form's data change to match the Main Form.

PS

It would be nice to see a jpeg of your updated Relationships. This will make answering future questions easier.

 

[hunterfan48]

My inventory table does have a BuyingTransactionID and SellingTransactionID that are a one-to-many relationship to Transaction ID in my table Transactions.

A single card ID may have more than one transaction ID. For example, that specific card ID may have been bought with transaction # 0023 and sold with # 0033 thus the need for two fields to show a buying and selling transaction.

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

Thanks