I have table with data like this in Access: I need result grouped by attr1, count attr2 and count attr3 only if value is “Yes”: I would appreciate any help with sql. Thank you very much in advance. Answer The expression for the third column is the challenge here. Consider an IIf expression: IIf(m.…
Tag: ms-access
Adding new records to Access table on an update to a linked table in Access VBA SQL
I have two table one (skillsMatrix) the other table is (elementTree) with columns [mediumElement], [ID] in table skillsMatrix the mediumElement is a lookup dropdown of the mediumElements in table two. I want to write a macro to update skills matrix table to add a new record “name”, “new topi…
Unpivot Data Using MS Access Query
Hoping someone with Access & SQL experience would be able to write some SQL code that I could use in MS Access to transform/unpivot some data. I’ve spent a day trying advise from others who have asked similar questions here, but I have no SQL experience and have failed miserably so far. Answer You c…
Access: Issue Creating SQL View Through VBA – Unions not allowed in subquery
First of all, let me preface this with something I almost always say in my few posts so far, which is that I’m not an IT/tech person. I’m an accountant who likes to dabble a little bit in SQL and so …
Multiple columns from one column
I track the aging of customer invoices. The below code example returns the balance of customer invoices by customer that are between 0 and 30 days old. However, I want to run one query that pulls separate columns into Excel for each customer’s balance aged between 0 and 30 days, 31 and 60, 61 and 90, an…
ms access, need to get all rows with a distinct column
I have a table called “parts” which stores information on electrical connectors including contacts, backshells etc. all parts that are part of an assembly have a value in a column called &…
MS Access SQL – Find numbers with decimals no end to Zero (,00)
I am having a table and i would like to find all numbers where it doesn’t end to ,00 but with decimal number like ,01 or ,23 or etc… I would like to create a sql query to create column c like iif(s IS ZERO AFTER DECIMAL, NULL, s) Is it possible in MS ACCESS SQL? Answer For positive numbers,
SQL how to select only single row using joining?
I have the tables: tblClients: tblKeys: tblEntries: lClientID and lKeyID (optional) refer to above tables. I need a SQL query which gives me the list of the users from the tblEntries only with maximum datDate field. Now i used the follwing SQL query: The problem i encountered is the datLastAccess can contains…
syntax error (missing operator) in query expression – VBA and Access
I build a query with this syntax: I did a test query in MariaDB, and it worked. Now, i am use this query in Access with VBA MSExcel, where it has the same structure and relationship between tables, but return error. message error vba: Syntax error (missing operator) in expression ‘t.id_empresa = e.id_em…
Select Top X records starting at record Y
I am using MS Access VBA to make a few queries. I have a list of about 4000 items and I need query 1 to be the top 739 items and query 2 to be the top 428 items starting at record 740 and I need query …