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
0answers
12 views

Return Value Corresponding to Latest Date (SQL)

Let's say that I have these tables: Widgets ID Widget 1 W1 2 W2 Widget_Making ID Person_Involved ID_Widgets 1 John 1 2 James 1 3 John 2 ...
0
votes
0answers
26 views

Why isn't my MS Access report showing info for all records? [on hold]

I have created an Access database that contains information about wastewater treatment facilities, compliance, and maintenance project financial needs. It also has information about which facilities ...
0
votes
0answers
15 views

Creating database wherein there are multiple tables with same fields under different categories

We have vehicle/equipment repair workshops located at different places, these workshops send a monthly report in a standard format in MS Word file (attached a sample copy)to the headquarter(HQ). The ...
0
votes
2answers
43 views

How to interpret timestamps stored as 10-digit negative integers?

I've inherited an undocumented MS Access database where the date/time values are stored in an INT column and the majority of values I've seen so far are 10-digit negative integers. How do you ...
0
votes
0answers
39 views

Condition table theory

Is there a good way to create a "table of conditions"? I have a huge table, where 4-5 columns are considered in a set of rules, and based on those columns default values must appear in other columns. ...
1
vote
0answers
40 views

ODBC connects but authentication fails on users table

We have sql 2008 running in mixed mode. The site where sql is running the ODBC connection works and ms access authenticates against the user table. We have an IPSec vpn to a satellite office using ...
0
votes
0answers
43 views

SSMA cannot read meta data (missing runtime components)

What I want to do should be rather easy: Use SSMA to transfer internal Access tables and queries to SQL-Server. On my Notebook I'm running SQL-Server 2017 and Office 365 (32 bit) As I have 32 bit ...
0
votes
1answer
28 views

Help me to RANK correctly in Access

I have such a table: I want to rank the people, as who has the more V1 and the less V2, V3, V4, V5 be on top. Please advise me SQL code for MS-ACCESS.
0
votes
0answers
14 views

Comparing Across Tables to Find Most Recently Modified Row

all. Ive been working with some Sales Force data to solve some data synchronization problems we have between two instances of SFDC. Quick Background We have two instances of SFDC (SalesForce) that ...
1
vote
0answers
21 views

Selecting Duplicates on All Fields

I have an MS Access (no laughing at the back) database I've used to import a bunch of IIS logs into. Having looked at the Excel files I pulled these in from, I'm worried going by the dates that some ...
0
votes
0answers
39 views

Corrupted Access Database

I have a big Access database, with aobout 150 Tables/Queries/Linked txt files/Macros/Modules. I was working today, and then from out of nowhere the database got corrupted. I googled, and forund that ...
0
votes
1answer
23 views

msaccess split db feature linked tables

We have an Access db that has around 300 linked tables (to SQL Server tables). To switch environments, we run some VBA code that re-links all these tables to the relevant server. But this seems to be ...
0
votes
2answers
66 views

Get value from one field, and paste it in to a new column

I'm relatively new to Microsoft Access, and just a beginner with SQL programming. I have a table in MS Access. It is an import from a .txt file. In the first column There is a String with a Number or ...
0
votes
1answer
40 views

Calculation of two dependent variables in Access Query

I'm having problem constructing below two variables in Access Query or Access SQL which are dependent on each other, please kindly help! a(n)=b(n)+c(n); b(n)=if(a(n-1)<>0,a(n-1),d(n)). n refers ...
0
votes
0answers
27 views

Access data form entry

I am relatively new to Access and have a question. I am creating a database for an assisted living community, regarding Dietary Restrictions. My question is: I would like to create a front end user ...
0
votes
0answers
25 views

Revenue Forecast Database Design

I am currently trying to design an Access database that will house financial data containing forecasting, actuals and prior forecast data. The current format of the Excel "database" is Customer ...
0
votes
0answers
24 views

How do I create a query to show values in their relevent years

I have a table with records that have start and end dates per record. I need to create a query that based on the start and end date puts the relevent value into a financial year. For example | Record ...
0
votes
1answer
86 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
37 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
62 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 ------------- ...
0
votes
0answers
29 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 ...
0
votes
2answers
49 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
248 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
34 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
53 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
15 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
31 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
103 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
33 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
130 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
18 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
25 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
15 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
14 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
48 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
21 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
286 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
99 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
301 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
21 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
45 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
110 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
19 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
33 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
36 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
205 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
391 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
99 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
31 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 ...