Child Of A Junction Table

[nanscombe]

Yes the "Note" field could have been placed in the table in this case.

However, yes there always seems to be a however from me, on other systems I have split the notes into a separate table. The ability to add notes (in memo fields) was required on several different places.

What I did was create a single entry form, accessed from a button, and pass it the PK of the parent record and a value to represent what "type" of note it was, eg "A" - note on a person, "B" - note on a transaction etc.

The form would then automatically populate a pair of non-visible fields with values that had been passed to it, as well as its own generated Primary key, as the record was saved.

When it came to reporting I used a couple of queries, based on tbl_Notes, which filtered out the type of note I was interested in at the time.

 

[Balthazar]

Some of you may be pleased to know that I've scrapped the child table and am just adding the Notes field, as is, to the junction table.

If it helps anyone with their projects, this is how the database is structured wrt to the transactions:
tblTransaction: { TransactionID (PK), TransactionDate, etc. }
tblItems: { ItemID (PK), ItemInfo, etc. }
jctItemTransaction: { TransactionID, ItemID, TransactionAmount, etc. }

@Nanscombe: Yeah, I did that too with some aspects of the database! Append queries are magic. I actually use Append queries to read in the code from the barcode scanner, for the individual items.

 

[RainLover]

Some of you may be pleased to know that I've scrapped the child table and am just adding the Notes field, as is, to the junction table.

If it helps anyone with their projects, this is how the database is structured wrt to the transactions:
tblTransaction: { TransactionID (PK), TransactionDate, etc. }
tblItems: { ItemID (PK), ItemInfo, etc. }
jctItemTransaction: { TransactionID, ItemID, TransactionAmount, etc. }

@Nanscombe: Yeah, I did that too with some aspects of the database! Append queries are magic. I actually use Append queries to read in the code from the barcode scanner, for the individual items.

I have been busy and therefore have missed some things.

This Note field. You claim that you are going to add it to the Junction Table. Just check that table to see if you can run duplicates. Simply copy a Record and paste it into a new line. I would prefer to attach the Notes to a more applicable Table. Note that I do not have a copy of your Database because you posted your sample in either 2007 or 2010. If you could convert it to 2003 and repost I would be able to see what you have done.

I also see the mention of a One to One relationship. Is this really happening and if so why would you do that instead of simply adding an extra field.

 

[Balthazar]

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship"
That's a good thing, surely? It means I can't accidentally add the same item twice, to the same transaction. There's a ProductTransactionQuantity field for that kinda situation.

But you're right, I should just keep an extra field. I was just a little nullophobic but the people here cured that

 

[RainLover]

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship"
That's a good thing, surely? It means I can't accidentally add the same item twice, to the same transaction. There's a ProductTransactionQuantity field for that kinda situation.

But you're right, I should just keep an extra field. I was just a little nullophobic but the people here cured that

I very much douubt that you have a One to One Relationship.

If in doubt look at your Relationship Window. The join lines are different. It will have a One on each end of the joining line.