SubForm & Append Query (2002)

Topic

New Reply

I have a Form (actually three similar forms: New Order, Order Detail, and Order History) that have a rather archaic design (see attachment

Reply | Quote

Replies

The append query/queries to transfer existing data to the new design wouldn’t be difficult. Before designing them, you have to decide whether the current order (Shipper/Consignee1, …2, …3 etc.) is relevant.

If the order is not relevant, you can do it like this:

1. Create a union query

SELECT ID, [Shipper/Consignee1], [ShConCity1], … FROM tblShCon
UNION
SELECT ID, [Shipper/Consignee2], [ShConCity2], … FROM tblShCon
UNION
SELECT ID, [Shipper/Consignee3], [ShConCity3], … FROM tblShCon
UNION
SELECT ID, [Shipper/Consignee4], [ShConCity4], … FROM tblShCon
UNION
SELECT ID, [Shipper/Consignee5], [ShConCity5], … FROM tblShCon
UNION
SELECT ID, [Shipper/Consignee6], [ShConCity6], … FROM tblShCon;

All fields to be transferred should be listed explicitly and tblShCom must be replaced by the actual name of the table. You will need to repeat an identifier field – I have named it ID.

2. Create an append query based on the union query (assuming that you have created an empty table with the unique fields.

If the order must be maintained, add an extra field SeqNo to the target table, and expand the union query:

SELECT ID, 1 As SeqNo, [Shipper/Consignee1], [ShConCity1], … FROM tblShCon
UNION
SELECT ID, 2 As SeqNo, [Shipper/Consignee2], [ShConCity2], … FROM tblShCon
UNION
…

Redesigning other forms and reports will be a chore, for the present design allows for 6 entries only. It is not practicable to expand them to accomodate an indeterminate number of entries, so you will probably have to start from scratch, and use subforms and subreports throughout.

Reply | Quote

Professor Vogelaar:

Is it just my dumb luck that you’re there when I need you, or do you seek out the under-knowleged? Great to hear from you again.

Armed with your response, I am off to experiment (with a copy of course – never will forget that lesson). I’ll get back to you when I stumble (notice I didn’t say “if”). I’ll work on the union query first (never done one before). Thanks so much for the input.

Reply | Quote

Well. . .not knowing the difference, I valiantly went about creating my first Union Query.
Query | New | Select Table | Select Query Type . . . hmmm, no Union Query here. Hit Query, look down list, still no Union Query.
SQL Specific. . .that looks interesting. . .ah ha!. . .”Union”! “Click”. . .what the. . .blank screen?

Ok, a little dramatic, but I wasn’t prepared for that. I see now that your instructions were for an SQL statement, and not for criteria expressions.
I’m going to try all this AFTER I get some sleep.

Reply | Quote

Hans,

I pray you’ll excuse my ignorance (again), but this is my first Union Query.
Please see attached Syntax Error Msg.

Thanks!

Reply | Quote

Bryan,

The word UNION means that the query must combine the results of the SELECT statement before it and the SELECT statement after it. So the general form of a union query is

SELECT ..,
UNION
SELECT …
UNION
SELECT …

This structure can be repeated as many times as needed. But there shouldn’t be a UNION after the last SELECT statement. You do have a UNION at the end, so SQL expects another SELECT. Remove this last UNION.

Reply | Quote

Hmm. . .
Resolved that, but. . .(attachment)
“reserved word” ?
I double-checked all the entries (names) from tblMaster2 to make certain there aren’t any errors, it all looks good.
Maybe a “space” or something in the expression?

Thanks for your patience.

Reply | Quote

Sorry, I should have seen that the first time.

The SELECT statements select a number of fields from a table. The fields are separated by commas:

SELECT Field1, Field2, Field3, Field4 FROM MyTable

In the SQL of your union query, you have put a comma after the last field to be selected. This makes no sense, since there is no field after it. So you must remove the comma after the last field (immediately before FROM) in each of the six SELECT statements.

Reply | Quote

Well, don’t feel too bad – as many times as I checked my entries I completely missed the ” ] ” after ShCondate.
I’m now getting a parameter prompt for the field “ID”, I assume I leave it blank to return all (1 – 6).
(I deleted several thousand records in this copy of the table, leaving 196)
A Data Sheet view of the Query reveals 331 records, which makes sense in that we have “compressed” 1 – 6 into a single field.

On to the append query?

If I am following this, I now need a copy of tblMaster2 to include all of the fields except those in the union query, and to include new names for the eight different criteria in the Union. Do I also need an “ID” field? (I’m a little confused as to how that fits in the picture).

Reply | Quote

Bryan,

Does the original table have some kind of unique identifier field? Or, to put it differently, is there a field that acts as primary key? If so, you should use the name of this field instead of ID. ID was just an example name I used. if there is no unique identifier, you should add a field of type AutoNumber to the original table, and use that instead of ID. If you don’t have a unique identifier, you’ll have no way to identify later on which records in the new table belong together.

If all goes well, the Union query should return six times as many records as the original table. You may not want all these records, actually, but we”ll take care of that later on. Try to get this right before going on to the next step.

Reply | Quote

Funny you should mention that. Early on I was going to inquire if “ID” was something I should change to my current unique record field, but forgot about it while trying to make the Union Query (I thought perhaps it was just part of the SQL staement). Yes, I have a field “ProNo” that identifies each record. It is not an autonumber however, but it is unique and sequencial. Back to the drawing table.

Thanks!

Reply | Quote

Big difference in the results (attachment).
I see what you mean now. There are 1176 records returned (exactly six times as many in tblMaster2)
I also see were we are going to have some null value records in the new table, as well as some name issues (“LOAD” vrs. “SHIPPER”)
Should SeqNo be included in the Append Query? I’m not clear as to how this will apply in the SubForm. We don’t want a SHIPPER to appear as a CONSIGNEE; or the SubForm to list the points out of order (ie: CONSIGNEE, SHIPPER, CONSIGNEE, rather than SHIPPER, CONSIGNEE, CONSIGNEE, CONS…)

Reply | Quote

Ah, now you’re getting somewhere! It looks like the Union query is OK now.

1. You must create a target table for the append query. One way to do this is as follows:
– In the database window, select the original table.
– Copy it to the clipboard (Ctrl+C)
– Paste it (Ctrl+V). Supply a better name than “Copy of …”, and specify that you want to copy the structure only, not the data.
– Open the new table in design view.
– Add a field SeqNo, of type Number (Long)
– Delete the fields ShipperConsignee2, ShConCity2 etc., keep only those ending in 1.
– Close and save the table.

2. Now, create the append query.
– Create a new query in design view.
– Add the union query (you’ll have to activate the Queries tab in the Show Table dialog)
– Drag all fields of the union query to the query grid.
– Under ShipperConsignee1, enter the condition Is Not Null in the Criteria line. This will exclude “empty” entries.
– Switch to datasheet view to check that the condition works correctly (you should now get less than six times the original number of records, because you exclude empty entries).
– Switch back to design view.
– Make the query into an append query (Query | Append Query…). Select the new table you just created as target, then click OK.
– If all goes well, Access will correctly assign the fields to be appended to, but check the assignment carefully.
– Execute the query. For an action query such as an append query, you don’t do this by switching to datasheet view, but by selecting Query | Run or by clicking the Run button on the toolbar.

Reply | Quote

I think it’s OK to ignore this error (attachment), but I thought I’d better check.
Also, sorry for the delay (When Mom calls long distance, you just have to take the call)

Reply | Quote

Some things just can’t be ignored. That didn’t work at all!
I tried removing the “No Duplicates” from ProNo (as well as the primary key)
I added AutoProNo as the new primary key with no duplicates, and got the (attached).

That seems right, but I dont know the effect on forms now that ProNo is no longer the primary key. This number is used throughout the app as a control number making each order unique (Order viewing, history, and is even used as the invoice number).

Let me know what you think.

Reply | Quote

Hi Bryan,

I was offline for some hours (dinner time here in Old Europe). You can keep AutoProNo if you like, but I would remove it, and change the primary key of the target table to include both ProNo and SeqNo. This will make the combination of ProNo ans SeqNo unique. See screenshot of what the Primary Key looks like in the Indexes window.

Reply | Quote

Thanks Hans:

I have made those changes to the index’s and only just now realized that the only fields in the table that have data are the ones we named in the Union Query. All of the fields from tblMaster2 are there, but not the other data. Do I now need a second Append Query to bring this into the new table? With the ProNo being unique (no dups) won’t this cause a problem?

Reply | Quote

What kind of fields are those? Are they specific to one Shipper/Consignee for a certain ProNo, or are they shared between all Sippers/Consignees for a certain ProNo?

Reply | Quote

They are shared.

Reply | Quote

You should already have or create another table in which ProNo is the primary key; this table holds or will hold the fields that are shared. This table shouldn’t hold the Shipper/Consignee info – you have a new table for that. The two tables should be joined (in the Relationships window) by ProNo. The table with primary key ProNo will be the record source of the main form; the new table with primary key ProNo+SeqNo will be the record source of the subform.

Reply | Quote

Got it!

Time to build the SubForm? I assume that I only need to include the shipper/consignee infor here, and not any reference to ProNo (due to the relationship of the tables)

Reply | Quote

You don’t have to display ProNo in the subform, since it will be shown (I presume) in the main form, but you do need it to link the main form to the subform.

Reply | Quote

Beautiful!

I only tried frmOrderHistory, but it works great.
Now, all I need to do is remember all this and get it into the primary app. If you recall, I started all this in a copy and the tblMaster2 is a cut-down version (196 records) of the original tblMaster (several thousand records).

I’ll then need to deal with all the other Forms, Queries, Reports, etc. that made reference to the multiple shipper/consignee fields. Plenty left to do, but I sure am grateful for your support and wisdom. You are THE man!

Reply | Quote

Good luck! Just to be on the safe side, make another copy of the “real” database before modifying it.

Reply | Quote

Point well taken, thanks for the reminder!

BTW, here’s a problem I hadn’t considered. . .
What do I do with all the extra space on the form now?

Thanks, once again, for your competent and always generous help!!

Reply | Quote

>> What do I do with all the extra space on the form now?

Rent it out as advertising space?

Reply | Quote

A genius with a sense of humor. . .God love ya!
CYA!

Reply | Quote

Well. . .

I thought we were free and clear, but I can’t add a new order. Everything on the main form still works, but when I try to enter the subform info it accepts the first line, but I get the error msg on the second line (see attach). No problem displaying the existing records info.

I need to go to my daughters, but will see if you had an opportunity to respond when I log back on in the morning (PST).

Thanks!

Reply | Quote

The union query you used to bring over the existing records into the new structure created a value for SeqNo. The subform you created doesn’t let you enter a value for SeqNo, so when you create a new record it gets the default value 0. This works the first time, but fails the second time because the ProNo / SeqNo combination already exists.

If you want to be able to set the order of the records in the subform yourself, you must put a text box bound to SeqNo on the subform. It is your responsibility as user to enter a valid value. You can also automatically assign a value to SeqNo when you start creating a new record. There are several ways to do this; if you’re interested, specify if multiple users will be editing / adding records in the subform for the same ProNo.

Reply | Quote

Thanks Hans, for hanging in there with me.

Small peer-to-peer with front-end/back-end. I don’t see that there would be much of a problem with two people creating an order at the same time and getting the same ProNo. I’m not completely versed in record-locking, but it seems to me that edits would never encounter conflicts either – I don’t believe two people can have the same order open. Thus, I think automatically assigning the number would be the best way when creating a new order. When editing an existing order, I suppose (?) the same logic would apply.

Relying on the operator to make consistently correct entries doesn’t sound like something I’d want to do.

Reply | Quote

The problem would be two people adding a record to the subform for the same ProNo at the same time. If the chance of this is negligeable, the following code in the Before Insert event of the subform will set the value of SeqNo:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.SeqNo = Nz(DMax(“SeqNo”, “tblRecordSource”, “ProNo = ” & Me.ProNo) , 0) + 1
End Sub

You must replace tblRecordSource by the name of the table that acts as record source of the subform.

Reply | Quote

Hans:

Got it! I never would have figured that one out.

I’m already seeing huge improvements in how I can format some of the reports now (with all the extra landscape). Having to include the info as a subreport rather than from the original table is something new to me, but no problems so far. A lot of work left to do, but I learned a great deal in this journey. Onward and upward!

Thanks again for your patience and invaluable input.

Reply | Quote

Ooops, forgot the attachment.

Reply | Quote