2022 Developer Survey is open! Take survey.

Questions tagged [ms-access]

Microsoft Access is a relational database management system that is part of the Microsoft Office suite.

Filter by
Sorted by
Tagged with
0 votes
1 answer
31 views

Extend Query to include additional column

I have the following query, which gives me (in addition to other columns) the Direction ID only: SELECT Users.Name, Users.Surname, Transactions.Timestamp, AccessPoints.Description as Door, Readers....
user avatar
1 vote
0 answers
260 views

MS Access 2016 M:N relationships return multiple columns instead of multiple rows from a JOIN

Let's assume a data model like this: Books could have multiple authors as well as authors could have written multiple books. If a book and its authors are selected by the following statement (this ...
user avatar
0 votes
1 answer
73 views

unable to add data to table because related record is required

I have an access database with my work that I am trying to insert into but I keep getting. 'You cannot add or change a record because a related record is required in table 'Projects'.' I'm running ...
user avatar
4 votes
1 answer
2k views

Designing a database which can track stock movements

I work for a manufacturing company which sells machines to customers which have a control unit installed in them. We need to keep track of the locations of all the Machines after they are sold to the ...
user avatar
0 votes
1 answer
43 views

Selecting values from one table which don't exist in the other

I have two tables, one containing a list of all orders and another containing a list of orders that have been invoiced. I have the following query which I'm testing in SQL Server Management Studio, ...
user avatar
  • 1
2 votes
1 answer
60 views

Is there a standard database layout for repetitive information?

I was recently tasked with creating a database to store a modest amount of data. I am learning as I go, but my main hold is figuring out the best way to layout the data. The problem is that for each ...
user avatar
1 vote
1 answer
53 views

Querying for 2nd highest numeric value

Database set up here (was too large for sqlfiddle): https://pastebin.com/Hd0DbQ5z This is an MS Access DB. I am looking to get the top score as well as the second top score. I have the following ...
user avatar
0 votes
1 answer
8k views

How can I merge data from an Excel file into an access database with multiple tables? [closed]

I have an access database with many internal tables. Each table is a category of electronic components (resistor, capacitor etc) In all tables, there is a manufacturer part number and a currently ...
user avatar
0 votes
1 answer
100 views

MS Access write conflict XMIN at (2^32)/2 -1

I have several clients running access 32 bit with a postgres database backends. A while ago, almost all the access apps started to return "write conflicts" when the end users would make changes to the ...
user avatar
0 votes
1 answer
25 views

MS Access Query summarizing 2 tables doesn't produce expected output

I cannot successfully create a query in MS Access 2016 that correctly summarizes data from 2 tables with similar but not completely identical fields. I have a fruit market. I buy each type of fruit ...
user avatar
  • 13
0 votes
0 answers
209 views

MySql to MS Access query conversion

I have a mysql query, which I want to use in MS Access. SELECT company_name, agent_id FROM ( SELECT company_name, agent_id, @rn := IF(@prev = agent_id, @rn + 1, 1) AS rn, @prev := agent_id FROM ...
user avatar
0 votes
0 answers
287 views

MS-Access - "Previous Record" Query

I'm working in an access database with the following fields (and others): Asset; Asset_Date; Start_Date; Start_Time; Office_Stop. Asset is a # field, Asset_Date is text; Start_Date is Date/Time; ...
user avatar
3 votes
1 answer
844 views

How to Prevent Timeslot Overlap

SQL newbie here. I currently have a 2-table database-"Customers" and "Bookings". This question concerns the "Booking" table, which has 3 attributes this question is about: BookDate BookStart BookEnd ...
user avatar
0 votes
1 answer
61 views

Policies and Procedures Database

I'm trying to create a database for work purpose, and I need you guys to help me. I don't know if my idea possible or not but i want to give a chance to apply it. As the title I want to make database ...
user avatar
3 votes
1 answer
45 views

SQL/Access: Iterate through a query's results to use in another's WHERE clause

Just to preface, I'm pretty terrible with databases in general (patience appreciated). I have a table structured as follows: TEAM |PERSON |GAME |SCORE _______|_______|_____|_____| ...
user avatar
1 vote
0 answers
629 views

How do I get MS Access 2016 to work with SQL Server 2016 where DATETIME columns are involved?

I'm working in an environment that has a lot of apps that are a MS Access 2016 front-end with a SQL Server 2016 back-end. Symptoms: When viewing data from a table where a DATETIME column is part of ...
user avatar
  • 11
0 votes
1 answer
24 views

Im trying to group two queries by year so that it will show the amount of tours done(by count of tour id) in that year [closed]

SELECT [school tours done showing year].[CountOfTour ID], [Visitor Tours Done Showing Year].[CountOfTour ID], Year([Date]) AS [Year] FROM [school tours done showing year], [Visitor ...
user avatar
6 votes
2 answers
2k views

SSMA for Access - Migration Assistant Wizard fails at link tables

When using SQL Server Migration Assistant for Access with the Migration Wizard, all goes well converting an Access (2016) database to SQL Server Express (2017) until I get to the Link Tables step. ...
user avatar
1 vote
1 answer
1k views

Convert MS access to MySQL DB using php

We are creating a system that has a button for converting MS Access DB to MySQL. Is there a way to convert MS Access DB to MySQL using PHP? I would really appreciate any inputs on this one. Thank ...
user avatar
  • 103
1 vote
1 answer
85 views

MS Access 2000 Application - SQL Server [closed]

I have an old MS Access 2000 application which connects to a SQL Server 2000 database. I need to migrate this database and application to a new server with SQL Server 2012. The Access application is ...
user avatar
3 votes
1 answer
10k views

Access "couldn't use ... file already in use"

I have an Access 97 database that is used by some software on multiple (up to 4-6) computers (running Windows 7). Once in a while (maybe 1-2 months) something happens where computers on the network ...
user avatar
2 votes
2 answers
907 views

Using Powershell can you insert a macro into Access?

I am using PowerShell to automate an export of an access databse (.accdb) to postgresql. As a step of that I am using a VBA script to convert all the table and column names to lowercase so they play ...
user avatar
  • 137
2 votes
1 answer
1k views

How to trust trust a document in access via PowerShell so I can trigger my database export marcos

Long story short I'm working on using PowerShell to automate a nightly DB conversion from access to PostgreSQL. In it's more basic form the script does the following. Copies the .accdb file to a ...
user avatar
  • 137
0 votes
1 answer
702 views

MS Access M:N Relationship query: exclude records with more than the requested values

I have a little problem when looking up records that have m:n relationships to other tables and therefore appear multiple times in link tables. I try to show it with a minimal example here: Let's ...
user avatar
1 vote
0 answers
2k views

How to Insert a static value into a column in MS Access during a query

I am currently switching data sources for one of my databases, now the old data used to include a column called 'Counter' every row had a 1 in it, this 1 was then used in excel formulas and summary ...
user avatar
  • 11
0 votes
1 answer
22 views

How to track specific item as part of a kit

Here is my situation. I have over 50 boxes each with a unique ID number. That number is tracked for inventory purposes. My problem is that inside each box there is one specific part that is tracked ...
user avatar
  • 1
0 votes
2 answers
41 views

MySQL Allows ODBC With Any Username

I have a MySQL Community database on a remote server and I am trying to get Microsoft Access to read data from it. This data is sensitive. The problem is, MySQL doesn't care about the username that I ...
user avatar
  • 111
0 votes
0 answers
498 views

Migrating Access 97 to Sql Server - Version Error

I have Sql Server Migration Assistant for Access v2.7, running on Windows Server 2016. On the same server I have 64bit version of Access 2016 and a 32bit version (I assume) of Access 97. I am able ...
user avatar
0 votes
1 answer
101 views

Need to join two similar queries that represent different years

Rather new to Access (using 2016) and very sketchy on my SQL skill set. First query totals sales by ALL THREE (user-supplied) criteria: Salesman Customer Product_Code [Year] Second query does same ...
user avatar
1 vote
1 answer
983 views

SQL MS ACCESS: Split row into multiple based on column value

I have table (SQL MS ACCESS) COUNTRY NO_OF_PAGES FRANCE 2 UK 4 GERMANY 1 I want output COUNTRY NO_OF_PAGES FRANCE 1 FRANCE 2 UK 1 UK 2 UK 3 UK 4 ...
user avatar
0 votes
1 answer
206 views

Access Sub Query Speed

I have three tables in Access: employees ---------------------------------- id (pk),name times ---------------------- id (pk),employee_id,event_time time_notes ---------------------- id (pk),...
user avatar
  • 101
0 votes
1 answer
624 views

Access Form: New Record command not available now [closed]

I have an access database with two forms. When the form is opened I got a pop up saying acNewRec command is not available now. I can hit end or debug. If I hit end and manually re-open the form it ...
user avatar
2 votes
1 answer
262 views

Access: Number format for VBA in queries of variant type

Lets say I have two queries like select iif(condition, col, null) from table select f(col) from table where col is a double-valued column f is a VBA-public function(value as double) as variant. ...
user avatar
  • 155
0 votes
1 answer
251 views

Creating a report that fills in when the user makes a data selection from a combobox?

I want to make a report that acts as an empty template, but when the user makes a selection from a combo box or pop up window, the template fills with all of that data related to that primary key. ...
user avatar
  • 101
1 vote
1 answer
146 views

MS Access: Issue with Autonumbers in the export

I have a question that might be trivial to solve, but I have spent some time on it and didn't manage to solve it. I am working on a database in MS Access, in which I keep track of the specifications ...
user avatar
3 votes
2 answers
21k views

How to compact the current MS Access database from VBA function

I want to be able to run the 'compact and repair' process from within a VBA module in the database. I have a batch process that I run occasionally, it drops a few old tables, re-imports them from ...
user avatar
0 votes
1 answer
74 views

Need help designing a query to pull a list of songs based on ranking and the last play date

I have a database of a list of songs used during a performance. The main table, tblSongs, has a Field that represents a Ranking, 1 being worst, 5 being best. The table is linked to tblEvents which ...
user avatar
0 votes
1 answer
78 views

Running MS Access queries for WAN hosted mySQL database

I have a front end MS Access running through a backend MySQL database via ODBC, I recently moved MySQL database from a local LAN server to a hosted WAN server on Amazon RDS. Now all of my queries are ...
user avatar
  • 1
3 votes
1 answer
888 views

Why is it specifically Immediate If (IIF)?

I am quite new to MS Access (and databases in generally) but I have noticed that IF() statements are actually written using the IIF() function. A quick search online told me that it stands for ...
user avatar
  • 33
2 votes
1 answer
542 views

SQL Server Linked Table in Access DB

I'm wondering if/how an SQL Server would be impacted by an Access DB that has ODBC Read-Only linked tables from that SQL Server on it. If a form event triggers a query on one of those linked tables, ...
user avatar
2 votes
1 answer
30 views

Store long number in MS access

How can I store 12 character long number in MS Access 2016, if the field cannot be empty?
user avatar
  • 218
0 votes
2 answers
124 views

Microsoft access to Sql Server Data Migration [closed]

I need to migrate some old MS Access data to a SQL Server Database. Which tool is easier for a newbie to work with: SQL Server Import/Export or Migration assistant for access(SSMA)
user avatar
  • 634
0 votes
1 answer
156 views

How can I turn each record on my Access table into 6 records?

I've spent all day googled one issue: please help. I have a table like the one below that I want to expand, turning each single record into 6 records. TableName: -------------------------------------...
user avatar
1 vote
1 answer
151 views

Totals on alias columns

I have a table in MS Access that has the following fields/data: School number Traditional Forms Sparring Traditional Weapons Combat Weapons ------------- ----------------- ---------------...
user avatar
  • 159
4 votes
2 answers
151 views

Access not properly translating TOP predicate to ODBC/Oracle SQL

I have a MS Access query that is based on a linked ODBC table (Oracle). The DDL for the underlying Oracle table is: create table road_insp ( insp_id integer, road_id integer, insp_date ...
user avatar
  • 1,375
3 votes
1 answer
1k views

Trace ODBC commands: What is the registry path for TraceSQLMode and where is SQLOUT.TXT?

In the Optimizing Microsoft Office Access documentation, it mentions that we can: ...edit a Microsoft Windows registry setting that allows you to see the commands that the Office Access database ...
user avatar
  • 1,375
3 votes
2 answers
120 views

why the last function in msaccess allow me to bypass the group by?

I have the following query on msaccess SELECT Trim([T13_RefSupplier_France_List].[Supplier_code]) AS Supplier_code, Trim([T13_RefSupplier_France_List].[Art]) AS Internal_reference, Trim(...
user avatar
  • 547
3 votes
1 answer
307 views

Strange UPDATE results in presence of merge replication

I have an MS Access front-end database that accesses an SQL Server backend. This (as far as we can tell) works flawlessly when the SQL Server database is not replicated. Once we introduce replication (...
user avatar
1 vote
0 answers
44 views

Save Access SQL code to text file or Python variable?

This may not have a good answer but maybe? So I have a number of Access databases that query a SQL Server through ODBC to generate reports. The SQL Server backend is being changed out and I need to ...
user avatar
  • 205
1 vote
2 answers
33k views

Access Query - Count distinct values [closed]

I have a dataset similar to the one below in Access: I am wanting to create a query which generates a table summarising how many sites each company is at. I.e. it will return something like this: ...
user avatar
  • 11

1 2 3
4
5
14