• Updateable Query Problem (97 sr2 on 95b)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Updateable Query Problem (97 sr2 on 95b)

    Author
    Topic
    #363290

    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”,”SLOT”,”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

    Viewing 0 reply threads
    Author
    Replies
    • #554018

      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?

      • #554019

        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

        • #554021

          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!

          • #554022

            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.

    Viewing 0 reply threads
    Reply To: Updateable Query Problem (97 sr2 on 95b)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: