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
Tag: ms-access
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 USDJPY should only have
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 with field size
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 different lengths, as it could be ABC_UK, ABC_CHR or ABC_I
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 need pass-through queries for some forms as I need to use DISTINCT
Define a temporary table within an SQL “select” query
I’m referring to MS Access SQL. Suppose I have a column with dates of birth of a population and the decades which these dates fall into. I want to create a query which will show how many people were born in each decade of a century. I know it’s going to be something like My problem is that there might
Unpivot Access Data Table
Good afternoon, I would like to unpivot an Access Table. I have almost one hundred of columns in Access and one column with the ID and I would like to unpivot, considering all the columns except de ID into one column called “type” (for example). How could I get it from Access? It doesnt matter if is by a SQL
sql – select partially unique values and concatenate non unique values into string
Say I have the following data set: How can I group by columns A and B and return a third column that is all unique values of C for that group concatenated together (in a comma separated string) using SQL. For clarity this is the resultant data set I am looking for: I am currently querying this in MS access,
Can you insert a custom value in an Auto-incrementing field in MS Access
The column auto-increments. User wants to insert a row with a specific number (I’ve confirmed it doesn’t already exist in the column). In SQL Server I’d do a quick SET IDENTITY_INSERT ON and insert statement. Is there a way to this for Microsoft Access or are they just out of luck. Answer Can run an INSERT action SQL. But then
MS Access – Selecting which table to merge
Is there a way to choose which table do I merge using SQL in MS Access through entering parameter value? Let’s say I have 3 tables: Users, 2021 and 2022. Any my query is: I get “Enter Parameter Value” window to filter the code that I want to check, but I would like to get the same to choose other