In Microsoft Access, I made a simple query with criteria to list all entries in a table between two dates. It was working well for more than a year, but now I think I am facing a bug… Between #2022-11-29# And #2023-01-26# causes a bug that corrupts the database and then I get the error message: Unrecogn…
Tag: ms-access
Access SQL Query to Count Unique Occurrences of One Field Matching Multiple Parameters/Rows, Some Identical
Struggling with ms-access’s flavor of SQL queries still, though I’ve made some progress (thanks to y’all). I have an event log table like this: Logs Table logID (auto#) modID (str) relID (str) DateTime (date) TxType (short) 1 1234 22.3 10/1/22 0800 6 2 1234 22.3 10/1/22 0900 7 3 1234 22.3 10…
Query to Sum values from one table based on values from another table, considering null values
I have two tables in MS Access. One for contracts, with contract id and total value. Another one for the payments made in each contract. So for example when a client gets a new contract for a total value of USD 100.00 then he can pay it in any amount he want up to the total value, like two payments
How do I used distinct to remove duplicates in this query?
I have the following table: Table I am trying to write a query (that I will include in another query) to display how many account numbers there are per symbol. I wrote the following query: The query displays like this but it counts the same account number multiple times per symbol. EURUSD should have 3 and US…
My update query in MS Access doesn’t seem to do anything at all
I’ve made a tool which lets users record information that is relevant to information pulled from a rota which is updated by someone else on an excel spreadsheet. In Access I’ve made a link to the data and want to use an update query to update existing information daily due to changes being made on…
Access – appending from two sources using unique multi-field ID. 1 field in 1 source formatted differently with leading 0s or letter
I am making updates to a database and am stuck with this problem. Appending to a destination with a unique two-field ID of, for example, field1 and field2. One source table, a ODBC linked table, has field1 text with field size of 8. Second source table is linked xlsx file and is imported with field1 as text w…
Find position of first number in string and split
I am attempting to find the first numeric character in an ID composed of a client segment identifier (ABC_), a geographic identifier (UK) and a number (1234567). The ID will without exception follow this logic: ABC_UK1234567 From this, I need to extract the number only (the location identifier will have diffe…
MS Access – How to properly use Pass-Through queries as source for form
In my MS Access FrontEnd connected to an SQL Server, I’m using a combination of pass-through queries and “normal” ODBC connection via file-DSN (for “easy bound forms”) for my forms. Within VBA functions and Subs I use some ADO connections to directly change data in the tables. I …
Can Someone help me write a query to determine which team lost a match AND a separate query to count each teams losses
I have tried the following code. I have attached an image of the table I am working with. I am working in Microsoft access. I am still very new to programming and I am trying to do this for a school project. Answer If you use MS Access you can use the IIF function to get the first result Please
SQL query to display rows of 2 different tables
I’m trying to figure out how to pull all rows from two different tables with the OutageID = X. The purpose of this is to view the current outage and all the revisions in one statement to see all of the changes. Below is an example I was trying. However, it puts it all in one row. I want it