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: vba
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 …
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
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 …
VBA SQL Intersect replacement for datasets from the same table. (MS Access)
I believe ‘Intersect’ is the solution to my problem, but implementing it in VBA is throwing me.. I have two tables: Items, for our purposes a single column of [ItemNumber] ItemProperties, 2 columns [ItemNumber],[ItemProperty] Each item as at least (no less then) 2 properties [ItemProperty] and as …
How do I create a recordset of nested queries from different databases
The following 2 queries are taken from tables on different databases I’d like to nest these in the following query However, I am getting an error: The connection cannot be used to perform this operation On the following line: rrst.Open mkQry I guess it has to do with MyQuery and MyQuery2, both being fro…
Can’t do Multiple Left Joins SQL Select (ODBC)
When I run the following SQL select statement with two left joins: I get the following error message in Excel VBA: “Run-time error ‘5’: Invalid procedure call or argument” When I run that exact same SQL without the left joins, it works without issue. I know the first SQL statement abov…
SQL query does not work in Excel but works in Access
For the code listed below, it runs fine except for the first SQL query. I’m pulling address and state information from the workbook, and running a query on the information to find the count of how many times the address appears in the table. If I run the code and stop it before the query is sent to Acce…
Access number identical fields with decimal number
I have a Table in Access and some field values are identical. If the field values are identical I want to have them numbered with decimal numbers. Is there a way to do this with SQL? Below is an example of my table and what I want to do. Answer You can use a query having two subqueries: Output:
Using Variables in a MSAccess Function with SQL
I’m struggling with entering a custom made ID number on all my tables to link all records together, trying out normalization. I have an EmployeeDetails table and a LoginDetails table. Its my understanding that I need a field called EmployeeID on both and I can use the SQL Select code I have to pull the …