Questions tagged [ms-access-2016]
The ms-access-2016 tag has no usage guidance.
40
questions
0
votes
1
answer
402
views
Data Type mismatch error when using function in SQL Statement
I have a function which gives Levenshtein Distance used for fuzzy search of a field.
Option Compare Database
' Levenshtein3 tweaked for UTLIMATE speed and CORRECT results
' Solution based on Longs
' ...
0
votes
1
answer
35
views
Using “OR” in SQL [closed]
I am new to sql and today my manager asked me to retrieve some customres informations using their customer ID so i used the following query:
SELECT *
FROM Customers
WHERE CustID = “11023” or CustID = ...
- 3
1
vote
1
answer
213
views
Unable to update SQL Server via Access, Reserved error (-7776)
I'm trying to use Access as a front-end for an SQL Server 2017 database. The objects I'm interested in are 6 temporal tables and a view joining them. I created an ODBC user data source and linked the ...
- 22.9k
1
vote
1
answer
265
views
3 Simple Queries SQL Student Question, Query Errors in Microsoft Access
The First Question is: "List the name of each trip that does not start in New Hampshire (NH)."
SELECT TripName
FROM Trip
WHERE State!='NH'; /* works with State='NH' */
The Second Question ...
- 135
1
vote
1
answer
718
views
How to refer to Field Name with a ? in it in MS Access VBA code [closed]
Simple question, but I am new to databases. I have a field called 'ActorRetired?', and in some VBA code I need to set the value of it to -1. But When I try:
Me.ActorRetired?.Value = -1
I get the ...
- 13
1
vote
0
answers
53
views
how to stop ms access 2016 from connecting back to microsoft?
I have a small program which compacts access database using DAO COM api. The program works, except around 50% of the time hangs on exit for about 15 seconds. So I have decided to investigate and try ...
- 123
0
votes
1
answer
132
views
Cannot login to a MS Access database
Below is some VBA code to authorize users to login into an MS Access database. I am trying to fix this code and I don’t understand what is the issue in this code snippet.
Private Sub cmdLogin_Click()
...
- 7
0
votes
1
answer
2k
views
How to convert columns into rows in MS Access [closed]
Please help me to understand, how to convert columns to rows in MS Access. I have the below table, I would like to convert as the below result table.
NoOf_A NoOf_B NoOf_C NoOf_D
200 300 400 ...
- 7
0
votes
1
answer
82
views
How to create columns with Count and percentage using MS Access Query
I have a data table below, I would like to create a query to produce a result table like below, with count and percentage, using MS Access Query. I am unable to create this.
Name
Alex
Alex
Bob
Bob
...
- 7
0
votes
0
answers
39
views
How do I link two Access Tables so if one Table gets updated both will?
I have an access database with a table that is linked to an excel sheet. I also have a duplicate table with the same data linked to a postgres database. My goal is to either link these two tables in ...
- 101
0
votes
0
answers
45
views
How can you effectively visualise a complex relational database?
I have an Access database which contains all the data from an open-source Japanese dictionary, JMdict. The database contains 24 linked tables, so reading it is complicated. The reason for this is that ...
- 181
0
votes
2
answers
250
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 ...
- 181
1
vote
1
answer
502
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 ...
- 181
0
votes
1
answer
94
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 ...
- 125
0
votes
2
answers
21
views
Table with two customer IDs that I want to relate to the entity
Let's say I have a transaction where Party1 and Party2 are associated, eg by a transaction - let's say Party1 sells something to Party2.
I have a list of transaction IDs, eg transaction1 is Party1 ...
- 1
0
votes
1
answer
526
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 ...
- 119
0
votes
1
answer
115
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
-------------
...
- 33
0
votes
1
answer
1k
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
1
answer
15k
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 ...
- 33
0
votes
1
answer
38
views
One primary key in two tables
I am first timer with ms access and also here
I am working on a database of library. I have two different tables which stores details one for books and others for Periodicals.
How can make ...
- 3
-1
votes
1
answer
40
views
Duplicate primary key
I am new in database and ms access.I am developing a database of income tax return form submitted by several taxpayers. Where each tax payer has an unique ID called ETIN (electronic taxpayer ...
- 99
0
votes
1
answer
1k
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 ...
0
votes
1
answer
609
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 ...
- 13
0
votes
1
answer
4k
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 ...
1
vote
0
answers
300
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 ...
3
votes
1
answer
3k
views
multi-condition left join conditions in MS access
Is there another way to write the following poorly performing in Access (Jet) SQL?
SELECT *
FROM
Det left join
(select * from Inv inner join Dep on Inv.SN=Dep.SN) as mass ON
(Det.RecSN = ...
- 968
1
vote
0
answers
734
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
805
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
1
answer
37
views
Would db corruption be further minimized if I remove querying on a backend table from the user's front end?
I have an MS Access database that was being used by 30 people. They all use it at different times of the day, but within any 8 hour block of time there might be 10 people using it. This database was ...
- 153
2
votes
1
answer
40
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
1
vote
1
answer
223
views
Compare all attributes in M:N relationship
I am new to database development and I'm sure my current problem is quite common. Unfortunately I couldn't find a solution yet - mainly because I'm not sure how to name it correctly ;) So if there is ...
3
votes
1
answer
2k
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,437
1
vote
1
answer
665
views
MSAccess: creating a new row in a related table and linking it back to the "master" table
In a hypothetical 'cars' database, assume there's a model table and a make table:
models
modelID int
model varchar
make int
makes
makeID int
make varchar
Model's make column has a foreign ...
- 11
0
votes
1
answer
47
views
How to query for something like "last active" per column in Access
Let's Say i have two Tables, let's call them
PC-Info
| ID | Hostname | Location |
|:---|----------:|:-----------------:|
| 1 | Hostname1 | This Room |
| 2 | Hostname2 | That Room ...
0
votes
1
answer
375
views
After running VBA, I lose exclusive access to the database
I wrote some VBA to import an excel spreadsheet easily into a table in my database. This way, the user just clicks a button on a form, and answers some questions about the file to be imported. The ...
- 13
1
vote
1
answer
295
views
Relating two fields of one table to one field of another
I have two tables that look as such:
Table1 Table2
ID | Appname ID | App | Dependency
------------ -----------------------
1 | App1 1 | 2 | 3
2 | ...
- 113
1
vote
0
answers
74
views
Create a column in a table based on the rest of the row and other table
I have two tables, and I'd like to select a range of positions from one table for each row in the other table.
I've tried to relate the tables in order to get new information but it seems like there ...
3
votes
3
answers
6k
views
JOIN ON syntax error in Microsoft Access 2016
I am querying a couple tables, and for some reason Microsoft Access 2016 continually informed me that my SQL query JOIN ON syntax is incorrect. Specifically, the JOIN syntax is highlighted, followed ...
- 51
3
votes
3
answers
10k
views
Access 2010-2016 Compatibility Error
I was just updated to Access 2016, while my users are still at Access 2010.
I opened our database in MS Access 2016 once, didn't make any changes, and closed it out, and now all the users are having ...
- 31
0
votes
1
answer
7k
views
Query showing a number instead of the column value
I have a query (query_clients) based on a table (Person). One of the columns I am pulling is a StateAbbr column, which is a lookup to the StateAbbr table. The primary key in the StateAbbr table is the ...
- 101