A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

append queries records with multivalue fields



 
 
Thread Tools Display Modes
  #1  
Old February 24th, 2010, 03:23 PM posted to microsoft.public.access.queries
Dawie Theron
external usenet poster
 
Posts: 1
Default append queries records with multivalue fields

I need to append a number of tables.

I make use of an APPEND QUERY and it works fine.

BUT, for one part of my application, I need to work with tables which
contain multi value fields (Attachments).

I could not find a way around that. Please help

Dawie Theron
  #2  
Old March 1st, 2010, 08:55 AM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default append queries records with multivalue fields

Dawie Theron wrote:
I need to append a number of tables.

I make use of an APPEND QUERY and it works fine.

BUT, for one part of my application, I need to work with tables which
contain multi value fields (Attachments).

I could not find a way around that. Please help

Dawie Theron


Why would you want to use multi-valued fields? Why not just create a child
table and do it the right way? I remember a looong time ago when the
FileMaker folks finally realized that MVAs were a bad idea... lots of fun to
sort out. I definitely wouldn't recommend using a feature like that.

--
Message posted via http://www.accessmonster.com

  #3  
Old March 2nd, 2010, 03:43 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default append queries records with multivalue fields

On Mon, 01 Mar 2010 07:55:45 GMT, "PieterLinden via AccessMonster.com"
u49887@uwe wrote:

Dawie Theron wrote:
I need to append a number of tables.

I make use of an APPEND QUERY and it works fine.

BUT, for one part of my application, I need to work with tables which
contain multi value fields (Attachments).

I could not find a way around that. Please help

Dawie Theron


Why would you want to use multi-valued fields? Why not just create a child
table and do it the right way? I remember a looong time ago when the
FileMaker folks finally realized that MVAs were a bad idea... lots of fun to
sort out. I definitely wouldn't recommend using a feature like that.


You should have heard the meeting between MVP's and the Access development
team at the MVP summit a few years ago. Blistering... we almost got thrown
out, and I think some feelings were hurt on both sides, unfortunately.

I absolutely agree. Bad idea, bad misfeature, unnecessary and hard to work
around... but added because there was a requirement to interface with
SharePoint.
--

John W. Vinson [MVP]
  #4  
Old March 3rd, 2010, 04:30 PM posted to microsoft.public.access.queries
Dawie Theron[_2_]
external usenet poster
 
Posts: 2
Default append queries records with multivalue fields

Thank you John and Pieter,

Yes, I certainly agree with you sentiments. Thing is, I am fairly new at
ACCESS (4 months) and the feature of being able to simply attach documents
directly to a field sounded like a good idea at the time.

I must say, it served me well for a while too. While I only used simple
queries...lol

Then I became a bit more confident with ACCESS and started to build more
complex queries. That is where the fun started. I only became awareof such a
thing as multi-vulue fields when the error messages popped up...:-) After
some reading up and much frustration, I am beginning to understand why.

At first I thought it was merely a "old school purist" thing, but I now
understand that it is fundamentally not a good idea.

So, How do I search more than one table simultaneously if I cannot append?
Or alternatively, how do I save documents in a database table? This
application is for a document control system, but the same would apply if one
wants to build a simple photo album?

The child table idea does not seem to solve anything, because I will then
simply move the problem to another table if I attach documents there, or not?

Looks like stumbled upon a touchy subject here...lol

Your response are really much appreciated.

Please continue with this thread, because I am open for suggestions and
definately willing to abondon the attachment idea, but need something to
replace it with.

Cheers
Dawie Theron

"John W. Vinson" wrote:

On Mon, 01 Mar 2010 07:55:45 GMT, "PieterLinden via AccessMonster.com"
u49887@uwe wrote:

Dawie Theron wrote:
I need to append a number of tables.

I make use of an APPEND QUERY and it works fine.

BUT, for one part of my application, I need to work with tables which
contain multi value fields (Attachments).

I could not find a way around that. Please help

Dawie Theron


Why would you want to use multi-valued fields? Why not just create a child
table and do it the right way? I remember a looong time ago when the
FileMaker folks finally realized that MVAs were a bad idea... lots of fun to
sort out. I definitely wouldn't recommend using a feature like that.


You should have heard the meeting between MVP's and the Access development
team at the MVP summit a few years ago. Blistering... we almost got thrown
out, and I think some feelings were hurt on both sides, unfortunately.

I absolutely agree. Bad idea, bad misfeature, unnecessary and hard to work
around... but added because there was a requirement to interface with
SharePoint.
--

John W. Vinson [MVP]
.

  #5  
Old March 3rd, 2010, 05:24 PM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default append queries records with multivalue fields

Dawie,
Maybe it's time for more details. Otherwise, I'm not sure if I'm answering
your question. Some table has a MVA field where you can add several
documents. This is essentially a one-to-many relationship sort of packed
inside your table. But Access is already relational, so why not just break
that one-to-may relationship right now and put the individual multiple values
into another table? Why would that cause a problem with your data?

What are you searching for? You can create a query joining the two tables
(primary key from parent record to foreign key to child table) and then open
that or base a read-only form on that... or use a subform...

Why do you need to save documents in your database table? Are you protecting
them from modification?

The child table idea does not seem to solve anything, because I will then
simply move the problem to another table if I attach documents there, or not?


Okay... please explain what you are trying to do with the data. I can't tell
why the "child table idea does not seem to solve anything". How do you
intend to use the attachments in the child table?

Pieter

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201003/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:39 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.