Questions tagged [ms-access]
Microsoft Access is a relational database management system that is part of the Microsoft Office suite.
664
questions
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....
- 238
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 ...
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 ...
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 ...
- 59
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, ...
- 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 ...
- 123
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 ...
- 45
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 ...
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 ...
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 ...
- 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 ...
- 121
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; ...
- 101
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
...
- 31
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 ...
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
_______|_______|_____|_____|
...
- 45
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 ...
- 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 ...
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. ...
- 191
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 ...
- 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 ...
- 11
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 ...
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 ...
- 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 ...
- 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 ...
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 ...
- 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 ...
- 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 ...
- 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 ...
- 271
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 ...
- 3
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
...
- 11
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),...
- 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 ...
- 1
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. ...
- 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.
...
- 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 ...
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 ...
- 831
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 ...
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 ...
- 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 ...
- 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, ...
- 21
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?
- 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)
- 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:
-------------------------------------...
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
------------- ----------------- ---------------...
- 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 ...
- 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 ...
- 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(...
- 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 (...
- 256
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 ...
- 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:
...
- 11