Search results

  1. E

    Closing Excel from Access VBA

    I haven't had a case for this yet. But you have to program cleanly and completely. MS Word is a little more critical. I know the statement of a Word expert who instantiates a new, self-created Word instance in its own class in order to then dispose of this instance cleanly and completely.
  2. E

    Update multiple records in sub form

    To edit several records at the same time, you will need to use an action query. As Pat correctly states, an update query destroys historical data. Under German tax law, relevant data must be retained for at least 10 years. But the school itself will also have its own historical development...
  3. E

    Closing Excel from Access VBA

    You might think Excel would be difficult. https://www.access-programmers.co.uk/forums/threads/closing-excel-from-access-vba.331172/#post-1920734 If you don't read, you can guess a lot. Code in #46: With Selection With xl.Selection Selection is an Application object and so must 1) at all and 2)...
  4. E

    Closing Excel from Access VBA

    sSQL = "SELECT * FROM QueryX WHERE " & Me.Filter Instead of * you would of course only list required columns.
  5. E

    Closing Excel from Access VBA

    Since I won't repeat myself, you should repeat working through the answers.
  6. E

    Closing Excel from Access VBA

    Content: You're just deleting it. Maybe you should export a planned query instead of some collapsed entity.
  7. E

    Closing Excel from Access VBA

    Dim xl As Object Dim WB As Excel.Workbook Dim WS As Excel.Worksheet This is stylistically inaccurate. Are you using early binding or late binding? This is typical for improper referencing of objects. If you run this code in Excel, it will probably work better because Excel knows itself and its...
  8. E

    Relations between products : Similar Products.

    Yes, same is same, and similar is similar. Don't be afraid of lots of records, that's what SQL is for and how to use it well. Of course, such duplications would not be recorded; one connection is sufficient. But the statement alone is certainly similar and therefore a classification is not...
  9. E

    Extract postal addresses from pdf mailing labels into DB

    With Power Query (Excel) you can read a lot of data sources and then revise them. So you could create a usable table and then add it to your database.
  10. E

    Relations between products : Similar Products.

    If ProductA is similar to ProductB and ProductB is similar to ProductC, then ProductA must also be similar to ProductC. This third pairing must also be reflected in the junction table, and then you will always find all products that have been explained and defined similarly to one.
  11. E

    Query return max value

    Since this will probably take longer, I have written down my thoughts in the attached demo. It will be shown: 1) how I imagine a useful may-to-many relationship, 2) how to UNPIVOT the original table, 3) how the data in the resulting list is divided into the tables of the relationship, including...
  12. E

    Correlation between two records of the same table

    SELECT P1.ProductName AS Product1, P2.ProductName AS Product2, P1.Size - P2.Size AS Diff FROM tblProducts AS P1, tblProducts AS P2 WHERE P1.ID < P2.ID ORDER BY Abs(P1.Size - P2.Size) Here, similarity to a number was interpreted as size.
  13. E

    Query return max value

    A real example should be based on a real database. So upload an example database with some representative records here, including the existing indexes and relationships.
  14. E

    Query return max value

    Is the table an export/import or a direct link to SAP? Furthermore, if you have a view, a query or a report in front of you, it also looks like a table. As well as: Not only developer gods work in SAP. Great software and software names do not guarantee outstanding performance. This table is a...
  15. E

    Query return max value

    Then you can forget about a reasonable query. SQL works row-oriented, not column-oriented. But with VBA and the appropriate skills you can solve everything, even if it is more complicated and time-consuming. With such an Excel-typical structure, you can also switch to Excel for processing. But...
  16. E

    Query return max value

    Your table does not correspond to the expected table design for a database. http://www.allenbrowne.com/casu-23.html The first correct step should be to break down this table into a many-to-many relationship. This cannot be used in a useful way. In the table for frequencies there should be a...
  17. E

    A counting problem

    SELECT Name, Database FROM MSysObjects WHERE Database > "" This allows you to determine the linked tables and the associated backend. You could also process this list directly via Recordset. You can also count easily, using a Count in the query or a RecordCount in the recordset.
  18. E

    How can I delete Duplicate Records from a table

    This is what I call an exclusive opinion. I can only shake my head at what some people think. For those who can read: My suggestion corresponds to the wording of the question. Or should I understand the counter-discussion to mean that it is a huge effort for those involved to write a delete...
  19. E

    How to select and also exclude criteria from same field

    You can also create a pivot table = crosstab query directly in Access. One of the above queries saved as Qx: TRANSFORM MIN(M.Profit) AS X SELECT M.Supplier, M.SUP_ID FROM mytable AS M INNER JOIN Qx ON M.SUP_ID = Qx.SUP_ID GROUP BY M.Supplier, M.SUP_ID PIVOT...
  20. E

    How to select and also exclude criteria from same field

    SELECT M1.Supplier, M1.SUP_ID FROM mytable AS M1 WHERE M1.Product = "Lemonade" AND NOT EXISTS ( SELECT NULL FROM mytable AS M2 WHERE M2.SUP_ID = M1.SUP_ID AND M2.Product = "Tea"...
Top Bottom