Using Update Query with Attachment Field

[Emily.G]

I'm really new to access and have been creating a database to store information on clients and the documents we keep on each client. I made a big mistake and set the database up with tables for each document type with their attachments and the information we input on the document. I had a bunch of tables connected to the client information table in 1-1 relationships so I went to combine it all in one big table. [sidenote: I read that this was horrible database design to have it all spread out in tables with 1-1 relationships plus it's keeping me from inputting all the information in one form because the client itself has to be inputted before I can input the documents since the document tables pull from the client name which isn't established when I try to put it all in at once. If I'm really not that in the red with having multiple 1-1 tables then I'd love it if someone could help me make a form that would let me put in the client information and the documents at once with out running into the issue of the form not knowing to create new records in the document tables with the name of the client automatically in there] That's where I'm hitting my issue;

I've been using an update query to move over all the text/date/number information from the documents tables to the new master table but it won't let me transfer the attachments I've attached because it's considered a multi-value field. We've already populated the thing and so it would really suck to have to go through by hand and re-attach the thousands of documents. Hopefully you guys can help!

Thanks,
Emily

[June7]

Would have to use VBA code to first save the attachments out to external folder then attach to field of other table. http://blogs.office.com/b/microsoft-...cess-2007.aspx

The 1-to-1 subordinate tables would be justified if master record will not always have related record in the associated table. By combining all into one, could end up with a lot of empty fields, something I choose to live with because it suits my needs. It is a balancing act between data normalization and ease of data entry/output.

To me, the bigger issue is multi-value fields and attaching files. Access has a 2gb size limit and attachments can gobble up this limit. I won't use multi-value fields.

[Emily.G]

Thanks so much for the response,
There are times when there aren't always related records in the tables because certain clients are required to submit certain documents and then not others. I think I'll leave it in the many tables then because it does eliminate a lot of empty fields and I had already made forms for the entry.

If you have time I have two follow up questions:
1. If I leave it as is, I've been trying to construct a query that displays all the documentation information for a client and I'm running into some issues (the same ones that prompted me to want to consolidate the tables in the first place). Like I said, there are times when one client may have sent over a waiver for our use while another hasn't because it's not necessary. Because everything is in a different table I'm not able to get the form we're inputting the bulk of information on a client to display the documentation there. If there is no related record in the other table for a document the form gets, for lack of a better word, angsty.

My solution was to use a select query to display the fields of the 4 different types of documentation using the criteria that the Client Name of the record displayed on the client form has to equal the client name in a record from the documentation type's table. My problem is, while Documentation 1 and documentation 2 may have records for Client Clyde, Client Clyde doesn't have a record in Documentation 3 and thus the whole query displays no results from 1,2,3, OR 4.

To circumvent this I did four separate queries for each Documentation table which is working but it's a real hassle not to display all of them together. Is there anyway to either display the results of the four seperate queries together but still have them editable or to get them all together in one select query?

I'm not sure anything I'm saying makes sense haha

2. If you don't use attachment fields to avoid overdoing the size, what are you using instead? We're worried about the size as it gets bigger.

[June7]

Query to join tables should be for reporting, not data entry/edit. Should use form/subform arrangement for data entry/edit. The subforms could be on a tab control on the main form.

As long as main table truly has 1-to-1 relationship with the other tables, one query should work to show data from all tables. Just need to get the jointype correct. INNER JOIN causes the problem you are seeing. Main table should have RIGHT JOIN to each of the other tables (Include ALL records from 'Client' and only those from ...)

I am not against attachment in records on principle but if there is possibility of exceeding 2gb, I would store path\filename to the external file as a string in text field. I am against multi-value field and if more than one file needs to be referenced for each client, would use a child table that has 1-to-many relationship with Client, regardless if attachment or external.

Splitting a db can help manage the Access size limit. My project has a frontend that is 42mb and a backend that is 122mb. Still a long way from 2gb for either but hopefully you get the point. Splitting is also recommend when there are multiple users.

[Emily.G]

Thanks! Worked it all out!