Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Updateable Query Problem (97 sr2 on 95b)

    I have a table that holds records for each job, incuding total hours allowed for each dept called Original

    I have a table where each record has the job #, dept and time allowed. We do engineering so a job may have 2 gears to be made, each with its own time allocations call Job Details.

    I have an input form for the job with a subform for input of the dept and op time.

    Either on exit from subform or on current on main form, I wish to run the following update query.

    UPDATE Original INNER JOIN [Job Details_Crosstab] ON Original.[Job #] = [Job Details_Crosstab].[Job #] SET Original.FAB = [Job Details_Crosstab]![FAB], Original.SAW = [Job Details_Crosstab]![SAW], Original.TURN = [Job Details_Crosstab]![TURN], Original.MILL = [Job Details_Crosstab]![MILL], Original.CNC = [Job Details_Crosstab]![CNC], Original.Drill = [Job Details_Crosstab]![Drill], Original.GEN = [Job Details_Crosstab]![GEN], Original.SLOT = [Job Details_Crosstab]![SLOT], Original.FITT = [Job Details_Crosstab]![FITT], Original.CylGr = [Job Details_Crosstab]![CylGr], Original.ThrGr = [Job Details_Crosstab]![ThrGr], Original.ProfGr = [Job Details_Crosstab]![ProfGr], Original.INSP = [Job Details_Crosstab]![INSP], Original.Misc = [Job Details_Crosstab]![Misc]
    WITH OWNERACCESS OPTION;

    the Job Details_Crosstab is as follows

    TRANSFORM Sum([Job Details].Hours) AS [The Value]
    SELECT [Job Details].[Job #]
    FROM [Job Details]
    WHERE ((([Job Details].[Job #])=[Forms]![Quick Card]![Job #]))
    GROUP BY [Job Details].[Job #]
    PIVOT [Job Details].JobType In ("FAB","SAW","TURN","MILL","CNC","Drill","GEN","SL OT","FITT","CylGr","ThrGr","ProfGr","INSP","Misc", "Goods In","Packing","TRUCK");

    but when I run it I get the following Error

    Operation must use an updateable query.

    I don't understand this message, what am I doing wrong??

    Regards, Allan
    "Heading for the deep end"

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Updateable Query Problem (97 sr2 on 95b)

    Crosstab queries are never updateable, and since you are joining to [Job Details_Crosstab] in your update query, then it won't be updateable. There's an extensive discussion on "updateability" in the help files.

    It appears that what you are doing is trying to save the results of the crosstab in the Original. Saving calculated results in a table is generally considered a bad idea, though there are some exceptions. Would a solution be to display the totals for each of the time allocation categories for the job in the header of the subform?
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Updateable Query Problem (97 sr2 on 95b)

    Wendell,

    If I write the totals into the header of the subform, can I then transfer these values to the Original table directly or into fields on the main form when exiting the subform?

    Allan
    "Heading for the deep end"

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Updateable Query Problem (97 sr2 on 95b)

    Yes, you could do that. All you would have to do is have code in the On Lost Focus that takes the values in the header and does an update to the Original table. That will involve using DAO and opening a recordset, which you indicated would be a challenge in your prior post.

    On the other hand, why do that? Letting users look at tables to see data such as this is generally a bad idea - somebody will edit it or do something they shouldn't - in fact we hardly ever let users see any table - we always use a form. So if they can see the values on the header of the subform, why save them in the table to begin with. They are transient values that will change the next time someone adds a record in the subform, so they aren't very useful. The one exception to the rule of not storing calculated values in a table is where you want to have an audit trail, but there you write the values to an archive table and never change them. Hope this makes some sense!
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Updateable Query Problem (97 sr2 on 95b)

    Wendell,

    The idea is to write the values into the original table for each job, The details from this table are viewed via a form that shows the original hours for the depts, the hours used in each dept via a subform to another table, and the difference shown and tallied so that the foreman can see at a glance the quoted time, actual time and remaining time for any job at a glance. this info is also available to the salesmen more for history purposes.

    Nobody sees the actual tables.

    This db has evolved over time as the requirements have changed and my knowledge and ability has grown. The fact that it is almost constantly in use also makes life interesting.

    Regards Allan.
    "Heading for the deep end"

Posting Permissions

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