Questions tagged [ms-access-2016]
The ms-access-2016 tag has no usage guidance.
40
questions
0
votes
1answer
34 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 = ...
1
vote
1answer
43 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 ...
0
votes
0answers
16 views
Error 2105 on Northwind derived Access database when trying to opening a blank form after changing the table source
I have an Access database which was built from the Northwind template, which is referenced here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases
I ...
1
vote
1answer
35 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 ...
1
vote
1answer
49 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 ...
1
vote
0answers
34 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 ...
0
votes
1answer
48 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()
...
0
votes
1answer
610 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 ...
0
votes
1answer
13 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
...
0
votes
0answers
20 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 ...
0
votes
0answers
32 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 ...
0
votes
2answers
60 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 ...
1
vote
1answer
47 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
79 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
2answers
17 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 ...
0
votes
1answer
180 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 ...
0
votes
1answer
101 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
1answer
274 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
1answer
8k 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
1answer
24 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 ...
-1
votes
1answer
39 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 ...
0
votes
1answer
676 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
1answer
436 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 ...
0
votes
1answer
3k 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
0answers
216 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
1answer
2k 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 = ...
1
vote
0answers
578 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 ...
0
votes
1answer
644 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
1answer
35 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 ...
2
votes
1answer
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?
1
vote
1answer
189 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
1answer
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
vote
1answer
514 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 ...
0
votes
1answer
46 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
1answer
307 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 ...
1
vote
1answer
183 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 | ...
1
vote
0answers
73 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
3answers
5k 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 ...
3
votes
3answers
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 ...
0
votes
1answer
6k 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 ...
