Questions tagged [ms-access-2016]
The ms-access-2016 tag has no usage guidance.
31
questions
0
votes
0answers
24 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
41 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
1answer
12 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
55 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
15 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
115 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
82 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
61 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
votes
1answer
1k 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
22 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
38 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
139 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
178 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
2k 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
85 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 ...
2
votes
1answer
991 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
379 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
353 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
169 views
Password char on MS Access 2016 inputbox
I am currently using Microsoft Access 2016 to develop a database for a music store and a hospitality store (both with the same owner). I call it "WakesMusicDB." It is close to completion and I'm just ...
1
vote
1answer
30 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
27 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
153 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 ...
0
votes
1answer
920 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
210 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
44 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
217 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
112 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
3k 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
9k 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
4k 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 ...
