Complex "notes" project and i am so lost...

[simpleton]

Jeez, I am so out of my league.

I've studied up on Access with Lyndia.com video training.
I've read the Access 2003 bible.
I think i've gotten down all the basics.
I am pretty good with SQL stuff, but this project is driving me crazy.

I have like 4 different tables all related to Customer Order Notes.
After I get it all set up, I need to be able to click a button and have everything pump into one row on our SQL server.

Am I going to use a macro to do this?
I don't know how to get this data to come together? Mainly b/c i'm trying to join data that is not even entered yet. I'm building a form that is going to join all this data with an Update button... and have combo-boxes for choices they may need. Like, how do i join based on an unselected combo-box???

Where should I go to learn this advanced stuff?
Any ideas would be most appreciated.
As i'm feeling a little overwhelmed.


-Thanks.

 

[The_Doc_Man]

OK, first things first...

Why FOUR tables? Why not ONE table with a "discriminator" code field that shows what kind of note it is?

Assuming you have some "key" field for the main record to which this must be tied, it would then be a simple enough query to just link the main table's key to the foreign keys of the notes table. If you don't specify a particular value of a discriminator, you get ALL discriminators. Once you have a query that joins the main to the notes (and you bring over enough to support this next suggestion), you can sort the query by prime key of the table, date of the notes entry, discriminator code, or some other field.

OK, now let's skin that cat another way. (MMMMEEEEEOOOOOWWWW!!!!)

If it really no-foolin' has to be FOUR tables, look at UNION queries that allow you to join multiple tables that at least partly resemble each other. The examples under the HELP for UNION query should explain it pretty well.

Now back to the "purist" viewpoint. I sincerely doubt that you really need four distinct tables, though it is your problem and you have to approach it in the way you understand it. Therefore, I will try to avoid making a dumb absolutist statement here.

It is my opinion that if there is a way to merge the data therein and just use some field with codes for each type of note entry, you simplify your design and cause yourself a lot fewer and less severe headaches down the road.

 

[simpleton]

Interesting.

I actually have 2 tables that are getting data extracted from our app. I need to make a way to copy this data to 1 row in our SQL table.. and then edit that one row with a whole bunch of other data. How can I append to the SQL, and then open that same row for editing?

 

[neileg]

You're not being clear about the business process. Without that advice is a bit tricky!

 

[simpleton]

Hmm.. Sorry.

Let me start over.

We have an application that runs manufacturing processes. From the order entry screen, I have created a macro (of sorts) that will extract data into Access (like CustID, Quantity, ShippingInfo).
I need a way to open that Extract in Access, and use it (along with some other info) to create an "Order Instrustions Note" (into SQL) specifying minute specifications for the order.

I've got the order note table setup in SQL, and I can edit it via Form in Access.
But i'm having trouble getting the Extracted data to populate into my new OrderNote.
I thought setting the "default value" for each field (to grab from the extracts table) might do the trick, but it just shows me #Name? when i go to a new record.

 

[neileg]

You need to use a query to join the table that holds the extracted data with the table that holds the additional data and base your form on that.

Or perhaps you can append the extracted data to a single table that also contains the additional fields that you need.

 

[simpleton]

Cool.
And then run an Append Query from that form to send the data into the SQL table?

 

[neileg]

SQL table? Do you mean SQL Server? If you do, then you can use Access to work in the SQL Server tables directly.