Recent content by Bob

  1. B

    Aggregate in subquery

    Genius! Thanks.
  2. B

    Aggregate in subquery

    Thank you arnelgp. This works perfectly. How can I then add CurrencyDate to the subquery as I'd like to show if there's a gap between TransactionDate and CurrencyDate?
  3. B

    Aggregate in subquery

    I have a table of payments in various currencies that needs conversion to local currency. tblPayments table looks like (PaymentID PK) PaymentID TransactionDate CurrencyCode PaymentAmount 17 4/3/2023 EUR 512.00 Conversion rates are provided by tblCurrencyHistory where PK is a composite...
  4. B

    Detail section does not adjust to height of page header

    Managed to solve problem by using an unbound report with two subreports. There must be a neater solution though... 🤔
  5. B

    Detail section does not adjust to height of page header

    I'm getting rusty. It has been a while... Two tables, one for person and one for gadget. One-to-many relationship, a person can own multiple gadgets. I want report to first displays all people contained in this report. Below is a list of all gadget owned by people. (The normal approach would...
  6. B

    Copy linked table to another back end

    Found the solution elsewhere. In case someone is looking for the same it was Bob Larson (MrExcel, MVP) who provided a solution that works in my case:
  7. B

    Copy linked table to another back end

    Hi there I have the usual setup with a front and back end. I want to copy three of my tables from the back end to another database (backup) at regular intervals. When using docmd.CopyObject and docmd.TransferDatabase it is the link that get copied (naturally) and not the table itself...
  8. B

    subquery question

    Hi. I have and tblOrders table with the usual PK/FK relation to tblOrderDetails table. ProductID in tblOrderDetails table is FK to tblProducts table where details about which warehouse the product is located. I need to create a query that return those records where an order is shipped from...
  9. B

    Link Excel to Access DB problem,Office 2007

    Saul, You can link directly. For questions please start a new thread. This way others can better make use of all questions and answers.
  10. B

    design of investment portfolio database

    I am building an investment portfolio database (another one, yes I know) I am currently analysing the transactions ie. Buys and sells of shares (& bonds) plus interest and dividend payments. There are more types of transactions but let’s leave that for the moment. All transactions have a...
  11. B

    How to handle Required property on table

    Hi I have two fields on a table, let's call them Category and SubCategory. Both rightly should have 'Required' set to Yes. Possible values for both fields are done with lookups so I use a combo in both places. When I select a category the subCategory will need to be requeried. All good when...
  12. B

    Good practice on transactions

    Thanks Banana and LPurvis. All very useful and very much what I was looking for. As I was planning to use ADO I thought of having a simple boolean controlling whether we were in a transaction or not. Declaring my connection object WithEvents should fire BeginTransComplete, CommitTransComplete...
  13. B

    Good practice on transactions

    ADO only chosen in case I want to move it to SQL server (perhaps MSDE). But then I would probably use a stored procedure for this, so... I haven't run into problems (yet!) but I have a vague memory that one should be careful, so I was just looking for some general guidelines. Thanks. From a...
  14. B

    Good practice on transactions

    Hi there I have a series of statements that needs to be carried out as a single unit (transaction). (If one fails, all should roll back) I intend to use ADO and command objects on the mdb backend. What is good practice? As I build and execute the various appends and updates I need to do...
  15. B

    Please help with normalisation

    Shockingly simple. Thanks. :o
Top Bottom