Appending a Linked Table through a form to another table?

[SPisarek]

Hello everyone!

I'm new to Access, and this forum, so I apologize if this topic has occurred often.
I am currently making a database for my work, and I am very new to Access. I made a union between the linked information table, and a similar table that I needed to keep as a manual fill. Now, I need to add information that isn't available on the linked table, to another form or table.

Example:


We have customers that send in orders via google doc and snail mail/fax. This is placed into our database. We have a few fields that are not seen by our customers to process orders. I need to be able to have those fields available and stored for my linked table, and put in a form so that I can append it as the order is processed.
For this, do I need to create an additional table to store the order processing forms? Or can I somehow add the linked data into the non linked (so they have their own order number and thus record), and then I have access to appending it during processing?

Thank you for your help!

[June7]

You said you made a union - do you mean a UNION query?

I don't understand the data structure. What are you linking to? What is purpose of this linked info? How and where is the customer order info entered? How is the linked info related to other tables?

Possibly another table is the solution.

[SPisarek]

So June 7, to answer your questions...
In order to get a full list of my orders and their shipping information, I made a Union Query. That helped me get a list to report.
The linked table is linked from a Excel document which I downloaded from Google Docs.
The customer info is entered both through the linked document, AND a form for the hard copy order forms.
The linked info is related to other tables through relationships such as location, type of shipper/shipping, etc.
Does that help clarify?

[June7]

Really prompts more questions.

You have orders in multiple tables - the Excel link as well as manual input to a native table? You have to periodically download the Excel to get new order info? Is this cumulative? If it is not cumulative, what do you do with the past downloaded records?

What are the 'extra' fields not in the link? These fields are currently in the native orders table?

[SPisarek]

What I do, is instead of downloading a new Excel file to add new orders, is I copy and paste what is in the google docs excel sheet into the linked table. So we have all our order records on file. So yes, I guess you could consider it cumulative.
The extra fields are in the native orders table. It's things like check boxes for each stage of shipping, date of shipping completion stage, who handled it, and type of shipping and/or shipper. These would not be on the google form.

[June7]

I am confused. You copy/paste from Excel into a 'linked' table? This is not a link to Excel sheet? What are you linking to? Why do you not copy/paste into the native table?

[SPisarek]

What I do is I look at the google doc excel-like response form, copy any new orders, and place them in the linked Excel document on my companies server.
The reason I decided to do that instead of the just copying and pasting into the native table, is because other coworkers don't know how to use Access, so they can easily update the linked Excel spreadsheet.
I have linked the Excel spreadsheet because it is easier to create a report to list all upcoming orders per month.

[June7]

Options I see:

1. import the records to the native table

2. separate table for the additional fields

[SPisarek]

With the tho options you have put forward, in order to connect the linked table to the table with additional fields, would I just create a relationship between them?
Thank you.

[June7]

Try it. I often don't bother with building relationships. I just build queries as needed.