Field relating two unrelated tables

[hunterfan48]

I have three tables.

The 1st table is for the sports cards. Each record is an individual sport card and the various columns and fields give detailed info about each record (card).
The 2nd table is for my transactions. Each record is a specific transaction whether is a card sale, card purchase, supplies purchase, etc. The various columns and fields have information about this transaction (record).

I have a third table (I guess I look at it as a middle table...not sure of the correct term for it) that generates a relationship between the 1st table and 2nd table.

I have two fields in my third table.

ID
Card ID

Both of the fields are linked to my 1st and 2nd table. The ID field is linked to my Transaction ID field in my 2nd table. The Card ID field is linked to my ID field in my 1st table.

Here's what I need to do now.

I need to be able to simply take each individual card ID and enter a transaction number for it. Now, some cards may not have a transaction number because I have cards already in inventory that I add to my database that haven't been bought. I just have them so I don't need to enter a transaction number yet.

Here's another twist though. Each individual card CAN HAVE UP TO 2 TRANSACTION ID'S. (A transaction for how the card was bought, and for how it was sold)

The only way I knew how was to do a query between table 1 and table 3.

I did a join type 2 between the ID field in my 1st table and card id field in table 3.

So, in this query I add the ID field from my 3rd table which stands for transactions and it shows all transactions for which a single card is associated. I tried to use this to create a form to add transactions to cards, but I found out this wasn't working.

LOL so does anybody follow here? Hopefully I made sense and I can get some clarification...

Thanks again!

 

[hunterfan48]

Here are some screenshots.

 

[hunterfan48]

and some more

 

[DavidAtWork]

My first thoughts are why do you need a 3rd table, if each transaction always relates to a card then all you need in the transaction table is a foreign key to CardID, the relationship would be one card has zero or many transactions. Does this sound too simple or am I missing something here.
David

 

[hunterfan48]

Hmmm...I appreciate your insight. Thanks for sharing.

For each transaction, there may be anywhere from 1 to 1,000 cards tied to a specific transaction. This would certainly cause a problem wouldn't it? Because if I had a FK for card ID in my transaction table, what would I do if there was more than one card ID??

 

[DavidAtWork]

Yes, I didn't realise a transaction can have many cards. In that case yes you need a 3rd table with foreign keys to cardID and transactionID, but you'd also need a primary key in that table, suggest an autonumber
Your relationship diagram shows cardID and transactionID as primary keys in tblTransactionID !!!! they should be foreign keys and not having a unique identifier could lead to problems and is probably related to the error messages you get.
David

 

[hunterfan48]

Another member told me that as well...I will be switching that ASAP.

However, why do I need a Primary Key field??

How will that help with tying more than one card to a specific transaction ID.

Eventually, I want to be able to design a query that will have a field for entering a transaction ID for purchases (BUY) and a field for sales (SELL).

From there, I would create a form based off this query that for every single card (card ID) in inventory, I would be able to enter a transaction number for the purchase and the sale.

 

[DavidAtWork]

Having primary keys (unique identifiers) is the basis on which relational databases are built. Any data tables should ALWAYS have unique identifiers. The way you have it at the moment with 2 PK's, the moment you try to enter a record in this table say for a cardID that already exists in the table in a different record, you will get a rude message from Access because you are trying to enter a duplicate value in a PK field as the second cardID is not unique.
In this case just add a new field to the table, set it as an Autonumber datatype and then set it as the primary key and save, it will auto generate id's for all your records, it doesn't have to be visible, ie you don't need to have that field on any forms, but it will save you possible problems later.
"Eventually, I want to be able to design a query that will have a field for entering a transaction ID for purchases (BUY) and a field for sales (SELL)."
Queries are for what they say, querying records, not usually used as basis for data entry, more used as a record source to drive a form or report
David

 

[hunterfan48]

Thanks again for the response.

I hear what you're saying about queries, but how else would I find a way to enter a transaction ID for selling a card and also buying a card??

That I'll have to keep working on...

 

[hunterfan48]

One way that might be a possibility is to add two lookup fields to my Card Inventory Table. (Although lookup fields in tables is discouraged)

I would create a 'Buy ID' and a 'Sell ID' field for my table. It would be a lookup of all the ID values in my transaction table. Wouldn't this work out ok? And if I'm able to do this in a table, shouldn't I be able to create a query that can do this?

 

[DavidAtWork]

Can you not just have a form which will show all transactions records which has a sub form showing the card(s) involved in each transaction.
When you create a new transaction, you will choose whether a sale or purchase, enter date and any other details related to the transaction, then add the cards involved
David

 

[hunterfan48]

I guess I could...I just don't think that would be the most effective way of inputting the data. Don't you think there's a better way?

Thanks for the suggestion...
Brady

 

[DavidAtWork]

I envisiage something such as clicking a button to start a new transaction, this starts a new blank trans record or opens a form with a new blank transaction, you specify whether supplies, sale or purchase (from a drop down), enter the date from a calendar control, enter any other transaction data, then you would click 'add/select card' involved, one at a time from a list, if it was a bulk transaction, then I think you'd need a way to group cards allowing you to select the entire group. Each time a card(s) is added, record(s) would be added to your 3rd table. When the transaction is complete, you'd see the trans record and below on a sub form all the related cards
David

 

[hunterfan48]

David,
I think that's a great idea and something I have toyed with myself. However, the tricky part comes in with the 'add/select card' from a list. That's gonna take some work.

I basically have everything else except this part which I'm working on now.

Thanks for your suggestion...your continued feedback is appreciated!

 

[gemma-the-husky]

think of your data first, not the implementation.

you have a card table
you have a transaction table

If a single transaction (sale) is in respect of many cards, then ALL you need to do is store the transaction reference in the cards table in the "sale transaction ref" field. You will just have a lot of cards storing the same value. you do not need a junction table for this.

Now what data field should we use for this. That depends. A LONG Number is always the most efficient choice for a linking field. If your current transaction table primary key is a STRING, then you could use this string within the cards table. Or you could add an autonumber (LONG) key to the transaction table, and use that. Personally, I prefer the autonumber way. I do not like to use strings for keys, and furthermore I prefer to use autonumber keys to manage table links, to avoid cascading update issues that might be necessary if the unique string identifier ever changed.

Now this scenario envisages a 1 to many link. Each transaction contains several cards, but a card can have at most a single transaction. In this case, you do not need a junction table.

But if, when you analyse your data you decide you need a different structure, because the above scenario is not quite right then so be it.

The point is, that the data structure has to be right to model your real world situation - and that once you have designed the structure in an appropriate way - then handling the data with queries, forms and so on becomes much easier, and natural. Given a good data structure, you can PRESENT and use the data in a choice of ways.


Hope this helps

 

[hunterfan48]

It does help...thanks.

I will say that your suggestion I have toyed with a few times, but it's not quite right so I haven't used it. The reason being is that a single card can have AT MOST TWO TRANSACTIONS. It may not always have 1, or 2, but I have to be able to allow for it.

Take this example

I purchase card A for $5. I then sell card A for $10 the next day. This specific card (Card A) has been involved with two transactions within my database. It has been bought (1 transaction) and sold (1 transaction).

So, if I only have a sales transaction reference field in my card inventory table, where will I put the transaction number for the purchase??

I always have to allow for 2 transactions...that's all there will ever be at the most.
I've been trying to come up with a query that I could use as a field in a popup form to fill the values in.

I've gotten close to finding a query that I believe will work.

Check out this thread...I'm just about there I believe. Please advise if you see something amiss or have an idea that would solve my above problem.

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

Thanks,
Brady

 

[gemma-the-husky]

i thought it might be that

but you only need 2 tables for that

in your card table, have a field for buyingtransactionID, and another field for salestransactionID.

I don't see any benefit in having a third table to carry this information.

 

[hunterfan48]

Hmmm....I'll mess around with it and get back to you on it.

Thanks

 

[hunterfan48]

So with this new method, I should be able to do everything I was able to do the previous way? I sure hope so because from 1st glance it looks a lot better. It will require me to do a lot of manual inputting and moving data around now but for the long haul it will allow me to be more efficient in what I want to do with my database.

Thanks for your help.

Brady

 

[gemma-the-husky]

So with this new method, I should be able to do everything I was able to do the previous way? I sure hope so because from 1st glance it looks a lot better. It will require me to do a lot of manual inputting and moving data around now but for the long haul it will allow me to be more efficient in what I want to do with my database.

Thanks for your help.

Brady

you ought to be able to fix your data with an update query. If before you could link a card to a (say) buying transaction, then you can still use this idea to drive an update query. You shouldn't need to reinput manaually