Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    SubForm & Append Query (2002)

    I have a Form (actually three similar forms: New Order, Order Detail, and Order History) that have a rather archaic design (see attachment
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SubForm & Append Query (2002)

    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
    ...

    <img src=/w3timages/blueline.gif width=33% height=2>

    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.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: SubForm & Append Query (2002)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: SubForm & Append Query (2002)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: SubForm & Append Query (2002)

    Hans,

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

    Thanks!
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SubForm & Append Query (2002)

    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.

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: SubForm & Append Query (2002)

    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.
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SubForm & Append Query (2002)

    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.

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: SubForm & Append Query (2002)

    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).
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SubForm & Append Query (2002)

    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.

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: SubForm & Append Query (2002)

    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!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: SubForm & Append Query (2002)

    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...)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: SubForm & Append Query (2002)

    Ooops, forgot the attachment.
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SubForm & Append Query (2002)

    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.

  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: SubForm & Append Query (2002)

    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)
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •