Recent content by mcalex

  1. M

    Best practice - Adding vs Editing

    As mentioned: The data relates to information in annual financial reports. There are about 200 pieces of information collected from each report, so in theory, only one table is needed to keep it at 3NF. I've now split the data into four tables, each with like data (operating revenue info...
  2. M

    Best practice - Adding vs Editing

    Is the best way: create one form and then open it differently for adding new vs editing existing records create one form to insert and one form to edit, where the edit form is bound to a query, and the insert form saves data following a button's click event. some other method? I guess I need...
  3. M

    using Count()

    @jdraw. table is pretty much as described: LocalGovernment ID Name RegionID LGType Ward ID Name LocalGovernmentID Election ID WardID ElectionDate ElectionTypeID TotalTurnout LocalGovernment 1 Metropolis 1 Shire 2 Gotham City 2 City 3 Bizarria (the capital city of Bizarro world) 3 City 4...
  4. M

    using Count()

    I'm trying to do a tally of election types using count, but am getting the wrong answer. In the database, LocalGovernments have Wards and Ward have Elections. An election can be one of two ElectionTypes ('In Person' or 'Postal'). Every Ward in a LocalGovernment has the same ElectionType. I...
  5. M

    Multi user environment

    sounds like you need to split the database into front end & back end. Tools/Database Utilities/Database splitter (at least in Access 2003, dunno if it moved in 2007). hth mcalex
  6. M

    Combine duplicate records into one.

    investigate Cross-Tab queries
  7. M

    'iferror' equivalent in access

    That sounds like a logic problem in the IIf(), or maybe there's a problem with QUERY_A itself (not just the Qty1 value) Might need to do some investigating (eg using Debug.Print) with what the values actually are so you can work out how to treat them. cheers mcalex
  8. M

    'iferror' equivalent in access

    not IS NULL; IsNull() You don't want the SQL predicate (I think that's the word), you want the vba function. For your first query, try: IIf(IsNull([QUERY_A]![QTY1]), 0, (IIf(IsNull([QUERY_B]![QTY2]), 0, ([QUERY_B]![QTY2]*[QUERY_A]![QTY1])))) hth mcalex
  9. M

    'iferror' equivalent in access

    I haven't come across iferror in excel - maybe it's a 2007/2010 thing. In any event, they managed to get by with this omission up til 2003, by using an if() wrapped around an IsErr(). The only difference in Access is you wrap the if() around an IsError(), so: If(IsError(x*y), <do nothing...
  10. M

    Copying Value of One field to the other

    I think you missed the
  11. M

    Copying Value of One field to the other

    Sorry, shoulda been clearer there. My bad. Nice save, thanks linq :-) @khwaja: that depends on how the data is put into the combo, but generally, the bound column is 0 and the shown column is 1, so replace: ("LocNo") with (0) and so on hth mcalex
  12. M

    Pulling data from one column based on the data from another column

    hi robruhr, cheers ;-) I've reattached the spreadsheet with how i see the ordergroup & prices tables see if that makes sense, fill in the rest of the data, and then try to get your query going. mcalex PS: I've added keys (the ID column) to show where one table points to another. You...
  13. M

    Chart problem!

    I think you need to change the table so it's only a 'single value field', and then use extra rows for clients with multiple letters. You should then be able to identify not only which clients have multiple letters, but also, tally how much of each letter is being used individually. hth mcalex
  14. M

    The 'one record in a subform' blues

    Hi Uncle G, thanks for responding. A couple of reasons, extra data from associated tables is being brought into the mix, I have a combo box to select the jobs (in the single sheet subform) rather than the navi buttons (coz i hate 'em), and I prefer to use sql queries rather than the table...
  15. M

    Pulling data from one column based on the data from another column

    It's possible, but a bit tricky. imo, you want tables on the styles, the order groups and the prices. Style table info would include the code & description. OrderGroup table info would include columns for group names (Price_1000-4999 etc) and the max and min amounts for that group (1000 and...
Top Bottom