Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jul 2009
    Posts
    185

    Unanswered: How to update data in two tables on delete.

    I have the following code:
    On deleting a record in flight I need to update the planes and battery tables.
    but how do I get the two tables updated

    Private Sub Form_Delete(Cancel As Integer)
    MsgBox "plane.iflights=" & [planes.iflights]
    [planes.iflights] = [planes.iflights] - 1
    [battery.ibtotal cycles] = [battery.ibtotal cycles] - 1
    MsgBox "plane.iflights=" & [planes.iflights]
    'I need to update the two tables add this point.
    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Update what, where and why?

    Cascading updates/deletes MIGHT do what you want...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2009
    Posts
    185
    When deleting a flight record I need to update the planes number of flights table for that plane (-1) and the battery number of cycles table by -1 for that battery.
    I am used to using get record and put record in Clarion for such things. I am having trouble figuring out how to force a upate to a table when I want after doing some calcs.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    My kneejerk reflex is to ask why you're storing derived data?

    Are you storing flights somewhere else?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jul 2009
    Posts
    185
    the main table is flights which is a log of flights what battery used and what plane with data about the end voltage and recharging mode. The battery table is a list of all the batteries description etc and most important the number of flights using this battery. The planes table is a table of planes, description etc and total flights on this plane.
    When I add the flights table I add 1 to number of [battery.itotal cycles] and plane.iflights. This works fine. But now if I delete a flight record I need to delete one from [battery.itotal cycles] and plane.iflights. and update their tables.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would recommend querying your flights table to get the number of flights associated with any given plan and battery cycles.

    SELECT battery_id, battery_description, COUNT(*)
    FROM yourFlightTable
    GROUP BY battery_id, battery_description


    By storing the count of flights along with the battery itself, you introduce the potential for inconsistent data. You KNOW how many times a given battery is used based on how many times it appears in the flight table. Relying on the number stored in your battery table instead of just going and doing the aggregate functions could result in an annoying discrepancy.

    You'd also be able to avail yourself of cascade updates/deletes since your relationships would make sense to the database.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jul 2009
    Posts
    185
    I see your point but the it would have search through the whole flight table every time I what the total cycles or total plane.flight. In my past programming days we would never design a program that way. But they were no database programs like Access or even dbase would have the same problem. But my ltable will never be that big do I guess it not is a big deal. It would fix the problem of changes and deletions.

    Also every report or other form needing this data would all have to do the search bad for a networked based app which this is not.

    But it appears your solution will work and mine is very problematic in a data base driven system.

  8. #8
    Join Date
    Jul 2009
    Posts
    185
    Sorry but I can't get the select statement right none of my books have much on select or sql. I guess it is another book to look for. Help did not help

    My flights table is "flights"
    no primary key ( a mistake I think should have a auto ID)
    battery number field [flights.Battery ID]
    how do I get the count to a varable?

  9. #9
    Join Date
    Jul 2009
    Posts
    185
    I appreciate the help.

  10. #10
    Join Date
    Jul 2009
    Posts
    185
    I am currently trying but get a complier error. But I cut and pasted from the sql query page. and added count(*)
    How do I display the count(*) I have searched the internet but can't find a good sample.


    SELECT Flights.[iBattery ID],count(*)
    FROM Flights
    WHERE (((Flights.[iBattery ID])=1));

  11. #11
    Join Date
    Jul 2009
    Posts
    185
    SELECT Flights.[iBattery ID], Flights.dDate, Flights.[iPlane ID],count(*)
    FROM Flights
    WHERE (((Flights.[iBattery ID])=1))

    Seems to work with no run time error but how do get the count??

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm surprised that doesn't error, you have no "group by" clause.

    Also, I don't think you want "dDate" in there. Everything you include in that statement will be used to "group" the data. So, by including date and plane id, you're creating little groups for each plane on each day. I don't think that is what you intended...

    Maybe try this:
    SELECT Flights.[iBattery ID], count(*)
    FROM Flights
    WHERE Flights.[iBattery ID]=1
    GROUP BY Flights.[iBattery ID]
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Jul 2009
    Posts
    185
    This work perfectly. Not sure why I had .dDate in there. I am only the programmer.
    Now how do I return the count to a variable I can then display on the form. I can't find a sample syntax!

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Can you provide a sample of the raw data, and then the result set you'd like to see?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Jul 2009
    Posts
    185
    It really simple I have a battery useage file with batter iid, description ,battery type .... On the battery form I want a field that show the umber of times(records) for given(default) battery . I need how to use that select statement in code and how to get the count in to a variable.

    If you really need a smaple data base I can send make one I can send you.

Posting Permissions

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