Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [ms-access]

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

0
votes
1answer
59 views

Unable to use today's date as a criteria in a query

Ok, been a good decade since I've worked with Access and SQL, so a little forgiveness please. Short version - when I add the query criteria to only display rows which match today's date, the query ...
1
vote
0answers
25 views

After reboot SSIS cannot connect to MS Access dbs

We rebooted our sql server 2016 machine. SSIS jobs that have been running fine are now unable to connect to access dbs. it throws the error: "XXXXX:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE ...
0
votes
1answer
45 views

Trigger Access table on Insert

I am using Access 2016 and building an in-house HR app for my team. I have a Position Table, an Employee Table, a Position_Employee Table and a Staffing Table Position Table ------------- ...
-1
votes
0answers
34 views

Copy SQL Data to Access

I have a customer with an Access 2016 database. He needs to move data from a SQL 2016 instance to the Access database on a periodic basis. We have tried installing the Access run-time engine on the ...
0
votes
0answers
18 views

Tables not showing up when linking SQL Server to Access using ODBC connection

I am learning to link the back end of a database in SQL Server express 2016 to the front end in Access 2016. The tables were originally in Access and then I exported them to SQL Server. Now I am ...
1
vote
0answers
31 views

Setting up an database for an electrical system [closed]

I am trying to set up a database to help working on an electrical system. However, I am not sure of what and how to set up the tables due to the complexity of the schematic diagram. Following is a ...
0
votes
2answers
41 views

Example of MS Access SQL IIF to T-SQL

Given the syntax rules shown below Access: IF(Condition, ReturnIfTrue, ReturnIfValue) T-SQL: CASE WHEN Condition THEN ReturnIfTrue ELSE ReturnIfFalse END What would the below be formatted to T-SQL ...
-1
votes
1answer
90 views

Select ONLY Max(date) when there's repeating dates

Here is a sample of the data contained in my Tracking table: tracking_id date_action action_id staffing_id ------------- ----------- ----------- ----------- 1 2019/03/04 4 ...
0
votes
2answers
33 views

How can I create such a query in MS access so that only customers with more than 3 bookings show up?

I have two different tables, the "customers" tables, with "customer ID" as the primary field and a few more fields such as Name, Surname etc. (All in text format). The second table is "Booking ...
0
votes
1answer
42 views

Unable to edit some records in SQL

I have a table we just transferred from MS Access to SQL Server 2016. It's a simple table for creating load numbers for shipping products. Using access we link to the SQL Server and we are able to ...
0
votes
1answer
13 views

Access query to locate changed records

I have a database of specimens for a natural history collection. The scientific names for the specimens have changed since the last name update ~10 years ago. The actual specimen entries are recored ...
0
votes
0answers
28 views

MS Access constant corruption issues since DFS, moved off DFS server, and still corrupting

Company has legacy MS Access databases that are being replaced by PERN (Postgres, Express, React, NodeJS) web app that is just now getting built. That is to say they still need to use the legacy ...
1
vote
2answers
70 views

How to prevent specific applications from connecting to Oracle?

Is there a way to prevent certain applications (e.g. MS Excel or Access) from connecting to Oracle database? A bit of context: My company uses an Oracle database as our live production database. We ...
3
votes
0answers
30 views

Migrating Access Saved Import Specifications to SSIS Package

We currently receive a multi-hundred column flat file from a vendor, and the tech prior to me had created an import specification in Microsoft Access to automatically split the file into the right ...
2
votes
1answer
66 views

Databse design for a part/subassembly list

We work with tracking the fabrication of large assemblies and sub-assemblies with a large flat Excel files. Usually separate spreadsheets for estimating material costs, fabrication costs, keeping ...
0
votes
0answers
16 views

Comparing each rows with the above row in Access and based on the results (= or <>) inserting different data!

I have a master table including ID, Date of report, Date of Installation. I need to write a formula in Access that if the ID of each row is equal to the above row then insert the Date of report of the ...
1
vote
1answer
22 views

Find all records with the same first 3 characters [closed]

Does anyone know how to return all records with the same first three characters in MS Access (I'm guessing this has to be done in the SQL view)? For example andria -doesn't match Angie -match ...
0
votes
0answers
12 views

Query getting data from another query triggers that source query or uses cached data?

In MS Access a Query Q2 can be so simple as SELECT * FROM Q1 (or largely complicated). Since Q1 can be another Query instead of a Table, the question arises. Does running Query Q2 trigger Query Q1 ...
0
votes
0answers
13 views

Importing Access Database file to postgres by pgAdmin3

I have many tables in a MS access database file. I want those tables to directly import in postgres (pgAdmin3). Thanks in advance.
0
votes
1answer
21 views

Linking tables in Access with unique ID

I am creating a database in MS Access that has 3 different tables with 3 separate types of information for one set of users, Personal, Business and Board. I have one table created with all the users ...
1
vote
0answers
19 views

Unique Records property in Delete Query

In Microsoft Access, if you create a delete query based on two or more related tables, you must open the Property Sheet and set the Unique Records property to "Yes". If you leave it set to "No", you ...
1
vote
2answers
161 views

Are there alternatives to Microsoft Access? [closed]

In my office, we often use MS Access for many applications (eg creating reports, data analysis and data processing) because it is convenient to use (easy data import, fast query building, easy ...
2
votes
0answers
76 views

Troubles inserting new records in linked SQL-server table in Access

Years ago, I upsized the back-end database from Access to SQL-server using the build in tool in Access. It worked fine but in the recent two weeks an error began to appear. This problem occurs for ...
0
votes
1answer
100 views

Access: Making a Pivot Table with Multiple, Same Values as Columns

First post ever, thanks for having me! Currently we have an Access database connected to two access control devices tracking hours for different statuses: Check In, Check Out, Break Out, Break In, ...
0
votes
1answer
20 views

How to Total all Records in a Field?

this is probably very basic but I can't figure it out. I'm trying to total two records to give the output: £2,100 in the Total field but they keep coming out separate. Any help? Thanks.
2
votes
1answer
31 views

Report transition from Access to Crystal (multiple queries)

I have just started a new job where I am responsible for building and improving reports. There is one report I am trying to move from Access to Crystal. The data is stored in an application called ...
0
votes
1answer
58 views

How Do I Query this Data in Access Without Creating a Circular Reference

I have two tables called Events and EventSeasons. Events define the main type of events that there can be, and EventSeasons stores the number of times that the events have occurred yearly. I'm trying ...
1
vote
0answers
18 views

How to use validation rule within database with drop down menus.

Building a scheduling database using access 2013 for my company. I currently have it set up so that only employees that have a rating of 3 or more for the particular job show up when you use the drop ...
1
vote
0answers
25 views

Postresql & Access synchronising

I would like to ask for hints / tips / pointers on the possible approaches that I might use for the following... I maintain a database locally using postgresql 9.5, hosted on a Linux server. However, ...
1
vote
0answers
31 views

SQL Server linked to AccessDB Update statement doesn't work in some scenarios

I have a SQL Server 2014 Express server linked to an Access DB. I am able to SELECT and view data in the access db without issue. However, when I run update statements they don't always work. The ...
1
vote
2answers
32 views

Access fails to group data! [closed]

In this example Query, SELECT [Account Transactions].ID, [Account Transactions].Transaction_Amount, * FROM [Account Transactions] GROUP BY [Account Transactions].ID, [Account Transactions]....
0
votes
1answer
94 views

MS Access - Creating a report based on a pass-through query that requires a parameter

I am attempting to make a report in Access that uses parameters as well as a pass-through query. I'm open to using a temporary table but the guides I can find using temporary tables all use an ...
3
votes
2answers
278 views

Local bus schedule structure/design

I'm Gergo Nagy, and I'm learning databases. My project is a bus schedule program, in android, but I stucked with the database part. My program will include 2 main parts: -If I click on a number of a ...
2
votes
0answers
50 views

Export table from MS Access to Excel while retaining Field Name and disregarding Caption

I have a MS Access Database, with each table containing the data of a particular questionnaire. I am building data entry forms for each of these tables, but as the field names may prove to be ...
1
vote
0answers
29 views

Jet SQL switch — Fill in 'n/a' for blank cells

My query isn't working as I expect it too. I am trying to create best_id and where blanks/null cells are set to 'n/a' . Also I use Microsoft.ACE.OLEDB.12.0 connection via excel to do an internal ...
0
votes
1answer
792 views

Microsoft Access is giving me network interrupted errors

We have just upgraded to window 10 and are using Microsoft Access 2016 (16.0.8431.2110) 32 bit. As a result of this upgrade we now have the issue of intermittent network connectivity issues. My ...
-2
votes
1answer
40 views

MS Access Bound Object Frame [closed]

I made an access bound object frame that has a datasheet form in it. The actual form has more than one row but the one in the bound object shows only one. Any ideas?
1
vote
0answers
68 views

Access Field Data Type Changed

I have several MS Access databases with tables containing fields that were set to the Data Type "Double" with decimal places set to "Auto" Access somehow changed all these fields to the Data Type "...
0
votes
2answers
2k views

LEFT JOIN with WHERE (Microsoft Access SQL)

Given following 2 tables a, b: table a ID job 1 farmer 2 physician 3 bank clerk table b bID ID lang job 1 1 en farmer 2 2 en physician 3 3 en bank clerk 4 2 de Arzt 5 1 ...
0
votes
1answer
46 views

MS Access: Capture ListBox Scroll Event?

An Access 2016 ListBox control has 10 items, only 1 showing at a time. My Goal: when the user scrolls up/down using the scrollbar arrows, I would like the item displayed to be automatically selected ...
0
votes
1answer
26 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....
1
vote
0answers
26 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
1answer
30 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
1answer
708 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 ...
0
votes
1answer
40 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
vote
1answer
35 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 ...
1
vote
1answer
42 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 ...
0
votes
1answer
2k 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
0answers
39 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
1answer
22 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 ...