Append Data - Dependent on the presence of a unique serial.

[sai541]

Hi everyone,



I have three tables and need a way to pull data from two (tbl1 tbl2) into the third(tbl3). The table being appended into contains a unique serial and no two serials will be the same, however within each record are incomplete fields. If the serial is present in tbl 1 or tbl2, i need it to pull other similar fields such as Date, Name and Ref No. into corresponding fields for that record in tb3.

I've been playing around with an append query but without success. What I'm hoping for here is a link or a guide to a similar process.

Thanks in advance for any suggestions.

Regards

Jeremy

[June7]

If I understand, you need to:

1. update fields in tbl3 with data from tbl1 and/or tbl2

2. add new records to tbl3 from tbl1 and/or tbl2 if not already in tbl3

No. 1 requires an UPDATE sql action. No2. would be an INSERT sql action.

Why do you need to do this? Are you trying to modify a database structure? If not, this appears to be duplication of data.

[sai541]

Numero uno. Tb3 will always have the unique identifier before tbl 1 and 2 see it. It is a duplication however, we need this particular setup because of our funding arrangements.

[June7]

Can't just do a SELECT query that joins tables and use that query for your requirements, which are what - report output?

Would be helpful to know more about table structure. Post sample data or even provide db. Follow instructions at bottom of my post.

[sai541]

I couldnt get it to 2mb so I've got some screen shots that might be able to help.

Tb1


Tbl2



Tbl3

[June7]

Sorry, no, too hard to work with the images. I don't want to try and recreate tables based on the images.

That looks like a small database. Why cannot get to 2MB zip? Did you run Compact & Repair? Make copy and delete some records.

Didn't address my question.

[sai541]

I completely appreciate that. I have taken all of the data and all irrelevant forms/tables and even with zipping it comes in at 3mb. In terms of table structure this is what I have.

Reference No is the identifier for the particular instance where a client has accessed relief services, client name and ID isnt used as a particular client may access our services multiple times within a year. Tbl Target $100 will have prepopulated data, such as the voucher type and number when we recieve them (as well as other fields I havent included which we have for accounting purposes). If I was using excel I would have a vlookup function that would populate the missing data (reference no, client name, client ID) from a voucher no found in Tbl Vouchers. We currently issue 10 types of vouchers, so in addition to Tbl Target there are other types. I'm aware its possible to run append queries to show which ref no, client name and client ID correspond to each voucher no in Tbl Target, but I havent yet had any success in this. I'm also aware that this is a duplication of data (which is a swear word in access land).

Any advice would be greatly appreciated.

TblData	Tbl Vouchers	Tbl Target $100
Reference No (PK)	PK (PK)	Reference No
Client Name	Date	Voucher Type
Dob	Voucher Type	Voucher No
Client ID	Voucher No	Client Name
Ect.	Client Name	Client ID
	Reference No

[June7]

You ran Compact & Repair?

Alternative is upload to a fileshare site such as Box.com and post link to the file.


Don't save Client name in tblVouchers, just the Client ID. Don't save Client name nor Client ID in tblTarget. The VoucherNo is a link to the client info through tblVouchers.

Not append queries, just a SELECT query that joins tables.


Are you saying you have separate tables for various types of vouchers? These tables all have the same fields? Should be one table with a field for voucher type.


Should avoid spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be tblTarget100Dol, VoucherNo, Voucher_No.

[orange]

Can you post a copy of your Relationships window?

What makes this data base so large?? Compact repair should remove unnecessary material.

Another option is to create new database (empty), then copy your tables, queries etc to new database.

[June7]

I agree, from what I can see of the database structure in the images, it should not be that large a file, especially without data. Although a few records would be helpful.