Combining data from one table + new data entered on a form to a second table! So confused.

[AlexTheGr8]

Hi All,



I hope I can word this correctly.

Essentially I have Table1 which contains all of our new clients (name, age, address, uniqueID) - that is updated daily.

What I want to do is have a form where our staff can click on a new client (from Table1) and see the details of their application appear in the form, PLUS a few extra blank boxes that are NOT linked to Table1. Once that client has been 'verified' by staff as eligible for a program, they can enter in "approved", and the staff name, and a few other fields into these blank boxes on the form.
Then they can click a button that runs some type of query (make table, update, append??) that will send this new information to a second table. This second table will contain all the info on the staff follow up, but have a join of the uniqueID to Table1, so we can analyze the information when needed.

I don't want anything deleted from Table1 (I want it to stay unchanged), rather I want Table2 to get a 'push' of copied data from Table1, plus all the new fields that were present in the Form. I'm not an Access master and feel a little out of my depth, but am willing to learn!

I feel like I'm missing a way to word this - hope I make sense! Any links are definitely appreciated

Thanks!
A

[ranman256]

Append query.
in the query take data from table1, with the form data, append to table 2.

[orange]

Instead of Table1 and table 2, it may reduce your confusion if you told us the "subject matter" of these 2 proposed tables.
The first sounds like tblClient. That table could have a field(s) for Approver, Approved_YN, ApprovedDate and these fields could be updated within your verification/approval process.
What would you call/name the second table?

[Gicu]

Don't duplicate the table 1 fields in table 2; just add the new fields from the form and the uniqueID from table1. You can put them together at any time using a query that joins the two by the uniqueID. If you use a form (bound to table 1)/subform(bound to table 2) it will automatically add the uniqueID in table 2 if that is set in the master/child linking fields property.

Cheers,
Vlad

[AlexTheGr8]

Hey! Thank you for your reply!
Yes so Table1 = ClientTable
Table 2 = ApprovedClientTable

It was my original plan to just have one table, with the additional fields in ClientTable, instead of having a second Table.
However, because the data in ClientTable is being updated daily through a daily update from new Excel files as they come in to staff, these Excel files won't have these extra fields in them. So I figured having a second table would eliminate any manual work for the staff. All new data not in the Excel would just be captured in ApprovedClientTable and a join can be made for reports to be generated.

I might be creating extra work for myself - so I figured I'd ask the experts!

Thanks!

[orange]

Is there anything in the proposed second table that could have multiple entries? If not, then 1 table with the extra fields could suffice.
Your new daily data from Excel could fill the regular fields and leave the "extra" fields empty. The "extra" field would get valued during the 'approval/verification' process.

[Gicu]

orange, I think the entire table 1 gets refreshed from Excel daily (meaning emptied and re imported), so any info in the extra fields would be lost in the process. I have successfully implemented the two table approach I described in my earlier post as long as the uniqueID (in my case an external student number) from Excel is constant.

Cheers,
Vlad

[AlexTheGr8]

Don't duplicate the table 1 fields in table 2; just add the new fields from the form and the uniqueID from table1. You can put them together at any time using a query that joins the two by the uniqueID. If you use a form (bound to table 1)/subform(bound to table 2) it will automatically add the uniqueID in table 2 if that is set in the master/child linking fields property.

Cheers,
Vlad

Thanks Vlad - and would this be an append query then? To append to Table2? And that will leave Table1 data as a stand alone, not deleted or altered?

Thanks!

[Gicu]

Not sure what you're asking. The subform will add a new record in table 2 if one doesn't exist or it will show you the info in the existing record based on the uniqueID in table 1. So the main form bound to ClientTable (which gets refreshed daily from Excel) would have in its header some search box (combo-box) to find the records, the details section will display the client info and also have a subform bound to ApprovedClientTable where you edit the non-Excel data.

Cheers,
Vlad

[orange]

orange, I think the entire table 1 gets refreshed from Excel daily (meaning emptied and re imported), so any info in the extra fields would be lost in the process. I have successfully implemented the two table approach I described in my earlier post as long as the uniqueID (in my case an external student number) from Excel is constant.

Cheers,
Vlad

Yes, I agree that is a possibility. The 2 tables approach is likely easier to understand also.
I was thinking of a "collector table" with autonumber. New records could be appended to the collector.
Those records with empty "extra" fields would be input to the verification process.
There was no indication of the status of records that were incomplete or did not pass the "approval process".

[AlexTheGr8]

Hi All,
Thanks for everyone's help. If anyone's interested this is what I did:
The main table remained the same, and what I did was create a form for it. When the staff click on the client that they want to approve, the information for the client appears in the form. Then I made unbound boxes where they can enter the new informaiton for the approval process. When they enter the new info in and click on the button that links to an append query, the query pulls the information from the cells in the form (that are either linked to the client table or unbound where they typed new information) and it appends it to a new table.
It's a little bit of a workaround but it's working so far! Thanks for the ideas, everyone!

Alex