Append query

%3CLINGO-SUB%20id%3D%22lingo-sub-2530628%22%20slang%3D%22en-US%22%3EAppend%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2530628%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20append%20several%20entries%20from%20one%20table%20to%20another.%26nbsp%3B%20I%20keep%20getting%20the%20error%20message%2C%20%22An%20Insert%20Into%20Query%20cannot%20contain%20a%20multi-valued%20field.%22%20In%20the%20criteria%20row%20I%20put%20%22between%2007%2F01%2F21%20and%2007%2F08%2F21.%22%20When%20I%20chose%20Append%20Query%2C%20I%20chose%20the%20correct%20table%20from%20the%20Append%20to%20Dialog%20Box.%20What%20am%20I%20doing%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2530628%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2531104%22%20slang%3D%22en-US%22%3ERe%3A%20Append%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2531104%22%20slang%3D%22en-US%22%3ECorrection%20to%20the%20last%20post.%20Make%20table%20queries%20are%20also%20not%20possible%20with%20MVFs%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2531154%22%20slang%3D%22en-US%22%3ERe%3A%20Append%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2531154%22%20slang%3D%22en-US%22%3E%3CP%3ESorry.%2C%20my%20original%20reply%20didn't%20appear%20so%20the%20above%20comment%20made%20little%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMultivalued%20fields%20(MVFs)%20are%20in%20many%20ways%20a%20poor%20choice%20and%20most%20experienced%20developers%20will%20recommend%20avoiding%20their%20use.%20See%20my%20article%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwww.mendipdatasystems.co.uk%2Fmultivalued-fields%2F4594468763%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.mendipdatasystems.co.uk%2Fmultivalued-fields%2F4594468763.%3C%2FA%3E%3C%2FP%3E%3CP%3EThe%20issue%20is%20that%20they%20depend%20on%20a%20deep%20hidden%20system%20table%20over%20which%20you%20have%20no%20control.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20error%20message%20is%20clear.%20MVFs%20cannot%20be%20used%20in%20INSERT%20(append)%20queries.%20This%20is%20the%20result%20of%20the%20deep%20hidden%20table.%20There%20is%20no%20simple%20work%20round.%20You%20will%20also%20be%20unable%20to%20use%20MVFs%20in%20make%20table%20or%20delete%20queries...though%20update%20queries%20can%20be%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERecommend%20you%20replace%20all%20MVFs%20with%20standard%20fields%2C%20one%20value%20per%20record.%20You%20will%20find%20life%20becomes%20far%20easier%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2546939%22%20slang%3D%22en-US%22%3ERe%3A%20Append%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2546939%22%20slang%3D%22en-US%22%3EThank%20you.%20Are%20attachment%20fields%20MVFs%3F%20Also%2C%20are%20you%20able%20to%20do%20remote%20sessions%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2550096%22%20slang%3D%22en-US%22%3ERe%3A%20Append%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2550096%22%20slang%3D%22en-US%22%3EHi.%3CBR%20%2F%3EAttachment%20fields%20are%20a%20special%20type%20of%20MVF.%20They%20have%20all%20the%20disadvantages%20of%20MVF%20fields%20and%20massively%20bloat%20the%20database%20size.%20Again%20much%20better%20to%20avoid%20them%20completely.%3CBR%20%2F%3E%3CBR%20%2F%3EWhilst%20I%20can%20do%20remote%20sessions%2C%20I%20cannot%20do%20so%20this%20week.%20What%20purpose%20would%20a%20remote%20session%20have%20for%20your%20problem%3F%3C%2FLINGO-BODY%3E
Occasional Contributor

I am trying to append several entries from one table to another.  I keep getting the error message, "An Insert Into Query cannot contain a multi-valued field." In the criteria row I put "between 07/01/21 and 07/08/21." When I chose Append Query, I chose the correct table from the Append to Dialog Box. What am I doing wrong?

12 Replies
Correction to the last post. Make table queries are also not possible with MVFs

Sorry., my original reply didn't appear so the above comment made little sense.

 

Multivalued fields (MVFs) are in many ways a poor choice and most experienced developers will recommend avoiding their use. See my article http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763.

The issue is that they depend on a deep hidden system table over which you have no control.

 

The error message is clear. MVFs cannot be used in INSERT (append) queries. This is the result of the deep hidden table. There is no simple work round. You will also be unable to use MVFs in make table or delete queries...though update queries can be done.

 

Recommend you replace all MVFs with standard fields, one value per record. You will find life becomes far easier

 

 

Thank you. Are attachment fields MVFs? Also, are you able to do remote sessions?
Hi.
Attachment fields are a special type of MVF. They have all the disadvantages of MVF fields and massively bloat the database size. Again much better to avoid them completely.

Whilst I can do remote sessions, I cannot do so this week. What purpose would a remote session have for your problem?
I still need your help. I tried setting up several attachment fields on place of the one multivalued field. It did not seem to work. These attachments are very important to what I am doing.

Will you be available to do a remote session this upcoming week?

@faxylady 

As already explained, attachment fields are a special type of MVF and should be avoided as well.

I never use either of those datatypes in Access and am unable to assist with making these work for you.

 

Instead of attachment fields, store the files outside Access and use a text field to store the file paths in Access. 

Instead of attachment fields, store the files outside Access and use a text field to store the file paths in Access.

How do I do this? How do I use a text field to store the file path?

I already have folders set up with jpgs' of the receipts. I don't know how to set up paths to them.

What data type do I use for the file paths?

Also, how do I upload files to you?

Please dont get disgusted or impatient with me. I am trying to learn.
Thanks.

@faxylady 

 

PMFJI:

 

As Colin has pointed out, MS in their "wisdom" introduced some "user friendly" features into Access which ultimately break down In Real Life situations because of their inherent limitations. That includes Multi-Value Fields and Lookup fields in tables. After years of experience, most Access developers learn to avoid them and also recommend others avoid them. 

I know of a couple of successful Access users who do implement Mult-Value Fields, but the situations in which they use them are very narrowly defined. For example, one that I'm thinking of has a series of charts that use scales of say, 1 to 5 or 1 to 10. Having an MVF with those values makes it easy to select a value for a data point on a scaled chart, but there is NO other user of them in that application. No input and no modifications are needed or allowed, so the inherent weaknesses of MVF's don't hurt.

I have a small demo on my website which illustrates a method of creating a membership directory with pictures of members. I suggest it as a reference because it  uses the method Colin suggests (external image files and links to them in a field in a table). Perhaps it can help clarify a bit. 

@George Hepworth 

Hi George

Thanks for stepping in. I hope the OP finds that helpful.

 

@faxylady

The file paths should be stored in a Short Text field. That gives a limit of 255 characters which should be more than enough for this purpose.

I have another example which may help: Folder Image Viewer 

 

I recommend you look at the two examples and try to adapt them for your needs.

By doing that, you will learn far more than if we just 'do it for you'

If you get stuck, upload what you have done and someone will look at it.

 

Good luck

Thank you so much for your help and consideration. There is quite a bit of material on Georg's post so it will take me awhile to work through. Hope you don't mind if I have more questions.
Thank you again. For some reason, I have not found the Upload key to use in this forum. Please help.

@faxylady 

GeorgeHepworth_0-1627349642676.png