Search results

Things like object name autocorrection and display of sub-data sheets are performance brakes and should be turned off during operation. In my case, they are already turned off in a new, empty database.

TOP 1 of many equal delivers all, since there is no distinction.

This works, but is not a convincing solution. In the first subquery, "All" is duplicated according to the number of records in table1. UNION without ALL then performs grouping, which reduces the number again. In the second subquery, further grouping is performed using DISTINCT. This process is...

Access generally works locally, both with VBA and when using its own SQL dialect (Jet SQL). Compact & Repair - Are you talking about a frontend or a backend? When repairs are necessary, you should always be a little nervous. A frontend can simply be replaced with a clean copy. Compacting a...

? DateDiff("n", #0:00#, #2:45#), DateAdd("n", 165, #0:00#) 165 02:45:00 You can easily convert the minutes as a long into a DateTime value and vice versa. DateTime is also a number internally. Text as a data type is more complicated, you want to calculate with numbers. I would look at...

SELECT BuyerID, COUNT(*) AS NumberPayments FROM tblPayments GROUP BY BuyerID HAVING COUNT(*) <= 2

A calendar table only develops its real charm when the relevant fields are indexed (date value unique, additional fields with date formats simply indexed) so that these indexes can be used to improve performance in JOINs, filters and groupings. Providing the date values as in the query shown...

@The_Doc_Man describes this in his last paragraph: https://www.devhut.net/ms-access-persistent-connection-in-a-split-database/ Frontends should be installed LOCALLY, as should the necessary Access installation. This is of course different for a terminal server, but even then each client should...

ACCESS Dependency Checker by Thomas Koester However, in the case of the problem mentioned above, I do not believe that the presence of queries per se is the cause. You should broaden your view to include domain aggregate functions (DLookup, DMax, ...) that are used en masse in queries, forms...

You change the same field RaditPodle twice. Do you notice that? If you are serious about a temporary table, you will delete it and create a new one. When you delete it, all the legacy data disappears.

A DAO method such as Openrecordset passes the query directly to the database engine (Jet/ACE). This understands SQL, but cannot do anything with variables and objects such as form text fields, which is why the query occurs. In the simplest case, you make a replacement Forms.ClientAppt.Cname =>...

Q2 code modified SELECT Q1.drcname, Sum(Q1.PrizeConvert) AS SumOfPrize FROM (SELECT ...) AS Q1 GROUP BY Q1.drcname; detailed SELECT Q1.drcname, SUM(Q1.PrizeConvert) AS SumOfPrize FROM ( SELECT decrace.drdate, decrace.drid, decrace.drcname...

How does this work? Select queries are simply executed and produce a result. If you use such a query (stored query or subquery as an SQL statement) in the FROM part of another query, this "embedded" query is executed first, so the data is immediately and up-to-date available to the query.

Instead of creating a fireworks display with DSum, it is better to calculate everything in a database in one query. Approach: SELECT Year(DateCompleted) AS QYear, DatePart('q', DateCompleted) AS Quarter, SUM(QTY) AS SumQty FROM InspectionsT WHERE DateCompleted BETWEEN...

The variations are then no longer so slim. As requirements increase, the appeal of a RegEx solution increases.

To answer the question more literally: ? RegExReplace("1234 johndon street", "(\d+)(\d{3})", "$1,$2") 1,234 johndon street ' for examble in a query SELECT RegExReplace(D.Proadress, "(\d+)(\d{3})", "$1,$2") AS Proadress FROM tblData AS D Private pRegEx As Object Public Property Get...

That's right. The crucial question is not the dialogue, but the conclusion of the dialogue and the subsequent use of the result. If you do not save the import as such, the information for column data types and others will expire. Can you use an import specification for TransferSpreadsheet...

means (for me): - The import specification is stored in two system tables and can be viewed there. - The import specification stored under a can be used directly as an argument via standard import or linking in the same way as with TransferText. Also directly within queries. That won't happen...

This is clearly wrong. In a typical Excel table itself there are no column-related uniform data types - in an "intelligent table" as a later imitation of a database table in preparation for Power Query (internally a modification of SQL Server) there are. With access via SQL /...

This has little to do with real databases; I would classify it as experimental programming. But technically that's not a big challenge either. 1) Bring the records from the three tables together => UNION query 2) Use it to create a crosstab query 3) Using a maketable query, you create a new...