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
17 views

Word file running an SQL command - How do I find the source SQL database?

A word 2013 file has a merge function that activates whenever this is open and it is "SELECT * FROM 'database'". As the final results have errors (the final word document feeds from the wrong field ...
0
votes
1answer
29 views

How to update a yes/no field to yes when all related fields in another table are marked yes

I'm new to database stuff (read: database idiot) and I am using Access. I have a table (let's call it "projects") that has a list of projects and one the fields (let's call it "actions_complete") for ...
0
votes
1answer
27 views

What's an alternative solution to using a null value for a foreign key field?

I have a few tables in my database which contain a Foreign Key field linking to another table. The problem is, the value for that field isn't always going to be present, and so sometimes I might have ...
0
votes
0answers
24 views

1 table with 2 same foreign keys to 3rd table with Primary Key

First time building a database in Access. I've been searching, but I did not find something to solve my question, so I finally decided to ask. It may be a really stupid question but what is the best ...
0
votes
2answers
35 views

How can I insert a record into a table that depends on a foreign key linking to a previous table?

Following on from this question. I have a dictionary file written in XML. I've written a program in VB.NET to parse this file and store all the data in Class objects, and now I want to write a ...
4
votes
2answers
1k views

What principles should I follow when entering data into a complex relational database?

I'm new to Databases, so forgive me if this is a basic question. I'm attempting to parse a complex open source dictionary, and output the data into an Access database, which is equally complex: I ...
0
votes
1answer
10 views

Is there any practical difference between using the Lookup Wizard to create a FK relationship, or using the Relationships window?

I'm very new to Access, so I'd appreciate patience if this is a basic topic. I'm creating a lot of tables which I need to link. I have two tables, Keyword and KeywordOrthography. KeywordOrthography ...
0
votes
1answer
35 views

Can't open Microsoft Access Database inside a Microsoft SQL Server Filetable Folder

We recently started to migrate the Data on our Fileserver to SQL Server (Version 14) Filetables and I encountered a problem with MS Access Databases (.accdb, Access 2007). When the .accdb file is ...
0
votes
0answers
16 views

Optimize data import to MS Access 2016 [migrated]

I am having a problem where I attempt to import a file to Access, but the import process bogs down and Access crashes. I am doing this in a desktop environment, and must use a specific desktop for it: ...
0
votes
1answer
44 views

how to professionally normalize my database - screenshots included

Hello database administrators. I got some excel spreadsheet with data of "my customers" and I'd like to create professionally designed database out of it. Can anyone tell me how this data should be ...
0
votes
1answer
19 views

Postgresql and MS Access unable to insert new data

I have a Postgresql server with a data base and table, columns dont matter. My issue is my primary key is set to BIGSERIAL, it auto-increments. However, on MS ACCESS when I create a form to insert new ...
0
votes
1answer
44 views

Generating an expiry date based on fields in different tables

I want a field that gives the date a certificate expires. I think i would create a query and add a field using something like this.. dateadd("d", [DaysToExpire], [DateCompleted]) My trouble is, [...
0
votes
1answer
61 views

See direct (instead of indirect) info about rented book in my “actors_directors” talbe dropdown list - Access365

I'm building a database of "actors_directors" who are renting "books" from my library - a kind of exercise to learn database basics :-) This is how my "actors_directors" table looks like. They are ...
1
vote
0answers
16 views

Modeling a system with optional components

I am working on an MS Access database that is intended to help keep track of a large scale implementation of many component parts connected together. I have created a working implementation based on ...
0
votes
0answers
18 views

Auto Increment Field

I am trying to implement a simple table in MS - ACCESS where im using two PK's for say a WEATHER table. PK LOCATION PK ID DATE TEMPERATURE I wish to increment the id based on the location, ...
0
votes
0answers
12 views

How to create a linked table to an SQL global temporary table

In my Access database VBA code, I would like to create a linked table to a global temporary table in SQL Server. I can't seem to get it to work. This is what I have tried: Dim adors as new ADODB....
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
16 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
47 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
42 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
41 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
80 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
15 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
40 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
68 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
30 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
97 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
41 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
68 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
36 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
55 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
572 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
35 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
70 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
35 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
133 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
197 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
19 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
27 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
82 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 ...