Storing Memo field in seperate table

[evanscamman]

I've read that it's best to store a memo field in a seperate table, so I've got 2 tables:

tblTrackingNumber & tblPurchaseOrderItem - one to many.

I can't figure out why I can't edit the tracking number in this query:

SELECT tblPurchaseOrderItem.PurchaseOrderItemID, 
tblOrderTrackingNumber.TrackingNumber
FROM tblOrderTrackingNumber RIGHT JOIN tblPurchaseOrderItem 
ON tblOrderTrackingNumber.OrderTrackingNumberID = 
tblPurchaseOrderItem.OrderTrackingNumberID;

I'm thinking this should be pretty simple right?
Thanks for any help.

Evan

 

[gemma-the-husky]

i cant see any benefit in storing a memo field in a separate table - just seems to add complexity to me

 

[evanscamman]

What if the memo field is only used on 1 of every 10 records?
Do the unused records end up eating a lot of space?

Thank you,
Evan

 

[Mike375]

It does not appear to, at least using A2003

I just tried an experiment. Made a DB with one table and two fields, one a number field and the other a memo field. Added 2000 records to the table, the number field only and after Compact the DB was 172K.

I then pasted a 20 page Word doc into a memo feld record and after Compact the DB was 228K.

However, I just added a text field set for 250 characters and after Compact the DB went to 6.7mb. Then reduced the text field to 10 characters and DB stayed at 6.74mb. Then removed the memo field and after Compact DB dropped to 6.66mb

 

[evanscamman]

Thanks Mike - that's good to know.

 

[LPurvis]

I too wouldn't say there's any concern based on required space, though if it was a rarely used field there could be a design argument for it being divided off.
(And of course - very much so if there's more than one, normalised off into a distinct table of related memo data).

However there is a case for this in that Memo fields are much more likely to corrupt that fixed size field Jet datatypes.
A corrupt memo field could then cause problems in your attempts to refer to the corrupt data row (even though the full Memo data is likely being stored on separate data pages if sufficiently lengthy).

I wouldn't expend too much time worrying about that either though.
It's the sort of extra consideration you might pay to an application - rather than first fundamentals.

Cheers.

 

[Mike375]

As a side note the memo fields I have are in different tables, very small, with just ID number, a text description field, date/time and the memo field. However, this has been done in the context of Leigh's post, that is, they are Many tables holding things such as file notes, Word correspondence etc.

 

[evanscamman]

In my case I am storing the Tracking Number for a order, and several orders may have the same tracking number. Wouldn't I be breaking all the normalization rules by storing this many times in the order table?

 

[evanscamman]

I'm totally stuck.
I think I do need to put the memo in a separate table for proper normalization.

I can't figure out any way to write a query that gives me the list of order numbers with an editable tracking number. It seems like it should be so easy!

Please help!
Thank you,
Evan

 

[Mike375]

I think you would be better off to start a new thread about your data base structure.

It would appear that this thread "Storing Memo field in seperate table" was not really about "space" and a memo field but rather a One to Many issue.

 

[evanscamman]

True enough about the thread title - I'll close it out now.

In case anybody else has the same (dumb) problem here's the deal:

I couldn't update my query because I was doing the join backwards.
One table: tblOrder
Many table: tblTrackingNumber

I had a field in tblOrder:TrackingNumberID and the query wasn't updateable. After seeing how LPurvis' "simple select" feature operated (one of the examples included in his signature), I moved the ID field to tblTrackingNumber:OrderID and everything started working.

Thanks for all the help and comments about memos!
Evan