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
Tag: ms-access
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
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 many as 700 properties. For each Item in the Items table, I want
Convert week number and year to a date in Access-SQL?
In Microsoft Access, I have a table where number of hours worked per weekday are associated to a project number, ISO-week number and year. Simplified, it looks like this: ProjectID WeekNumber YearNumber Monday Tuesday 1 1 2022 5 6 1 2 2022 7 8 I am trying to set up a query where all weekday columns (monday to sunday) are
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 from a different database. Is there a
How do I change a list of customers with multiple lines, and details into a single account line with categories
Example Table, Turn this CustID CustName SalesRepName SalesRepSpecialty 1 ACME Corp Steve P Servers 1 ACME Corp Susy Q Network 1 ACME Corp John D Storage 2 MicroStuff Auntie Irma Network 3 O-Rickle Daddy Larry Servers and turn this into CustId CustName Server Network Storage 1 ACME Corp Steve P Suzy Q John D 2 MicroStuff Auntie Irma 3 O-Rickle
Missing query data when using Sum
My database in Microsoft Access looks like this: Every Client can have many assistants. Every Assistant may have one client or no clients at all. Assistant have a Nice field which is Boolean, indicating whether the Assistant is nice. I need a query where I can get all of the clients, together with a boolean value indicating whether they have
stack or union multiple fields in MS Access
Beginner’s question here… I have a table of tree measurements being 3 fields: – ID, Diameter_1, Diameter_2 & I wish to get to these 3 fields: – ID, DiameterName, DiameterMeasurement Input and Desired Output Though it results in only 2 fields. How may the field: – DiameterMeasurement be brought in? Many thanks 🙂 Answer You were on the right track
In access/SQL how do I make only the null values in a column equal to value in another column?
I want to make only the blank cells in ATTRIB_VALUE equal to the values in ATTRIB_VALUE_NUM_9. Answer Something like this: I’m not sure what your table name is based on your post, but I think that’ll get you what you need.
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 Access, I