Skip to main content
Share Your Experience: Take the 2024 Developer Survey
2 votes
Accepted

JOIN ON syntax error in Microsoft Access 2016

The solution I realized needed the INNER JOIN syntax rather than JOIN ON syntax for Access 2016 as JOIN ON without a combination of INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, etc. does not work ...
JMK's user avatar
  • 51
2 votes

JOIN ON syntax error in Microsoft Access 2016

Access requires INNER JOIN. The simple JOIN (which should default to INNER JOIN according to the SQL standard) is not allowed in Access.
ypercubeᵀᴹ's user avatar
2 votes
Accepted

Trace ODBC commands: What is the registry path for TraceSQLMode and where is SQLOUT.TXT?

I managed to dig up the path from a comment on this page. On my machine, with Access 2016, the TraceSQLMode registry key is found here: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\...
User1974's user avatar
  • 1,467
2 votes
Accepted

Store long number in MS access

Try the following solution:(Is Null Or Not Like "*[!0-9]*"):
József Kökény's user avatar
2 votes

Linking tables in Access with unique ID

Each table should have a UserID column, used to "link", or relate, rows in the User table with rows in the Personal, Business, and Board tables. In SQL language, this looks like: CREATE TABLE ...
Hannah Vernon's user avatar
  • 70.1k
2 votes
Accepted

Select ONLY Max(date) when there's repeating dates

Akina has already pointed out critical problems with the data... that there is no existing way to determine the proper ordering of the Tracking rows. It is tempting to just skip this question, but ...
C Perkins's user avatar
  • 477
2 votes
Accepted

Unable to use today's date as a criteria in a query

You missed the time portion of [FG Orders by Date].[FG Date] as it is defined as [FG Date] DATETIME DEFAULT Now() Your table displays the data formated (only date part), ALTER TABLE [FG Orders by ...
ComputerVersteher's user avatar
2 votes
Accepted

Using “OR” in SQL

Better is a relative term as different people will have different opinions got different engines. That said, a simpler method to search for multiple records using the same column would be IN. Your ...
matigo's user avatar
  • 2,028
2 votes
Accepted

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

Creating a relationship through the relationship pane does one thing: it creates a relationship (obviously). The lookup wizard, by contrast, can do multiple things: Create a relationship (identical ...
Erik A's user avatar
  • 248
1 vote
Accepted

how to professionally normalize my database - screenshots included

You have a customers table. a jobs table. You normalize the 3 Profession columns by moving that information into a linking table which contains Customer ID Job ID something to represent "1st","2nd",...
Michael Kutz's user avatar
  • 4,829
1 vote
Accepted

Unable to update SQL Server via Access, Reserved error (-7776)

Access (I guess) does not seem too fond of SysStartTime and SysEndTime columns. If I mask them in a view and link the view in Access, both update and insert works. Example: DROP VIEW dbo....
Lennart - Slava Ukraini's user avatar
1 vote
Accepted

3 Simple Queries SQL Student Question, Query Errors in Microsoft Access

the first is simple access has no != SELECT TripName AS Ausdr1 FROM Trip WHERE ([State])<>'NH'; the form of the second is wrong and you should start to use proper JOINs a comma for tables ...
nbk's user avatar
  • 8,422
1 vote
Accepted

How to refer to Field Name with a ? in it in MS Access VBA code

You can't have special characters in a VBA name. The bang operator ( the ! in Me![ActorRetired?] = -1) is just a shortcut for string based name lookup. It's not syntactically clear so I recommend ...
HackSlash's user avatar
  • 161
1 vote
Accepted

Cannot login to a MS Access database

Instead of looking for matching username and password from the table, I would suggest you to use login system explained in this link. You just need to import forms and code from demo database from the ...
Pacman's user avatar
  • 38
1 vote
Accepted

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

As Akina said in the comments, try to avoid breaking relationships and referential integrity in your databases. If there is a documented need to have a value in there that indicates "none", "unknown", ...
John Herbert's user avatar
1 vote

Tables not showing up when linking SQL Server to Access using ODBC connection

I just had your problem. Here's how I fixed it. I was testing a new SQL database, a copy of an existing one that worked fine. My problem was the same as yours: I had a view that wasn't showing when I ...
Mentat's user avatar
  • 11
1 vote

One primary key in two tables

The two options would be: Model them in the same table with a property to distinguish their type (such fields are often called a discriminator) and all the properties of both types. Model them with a ...
David Spillett's user avatar
1 vote

Duplicate primary key

I cannot set ETIN (which is 12 digit unique numbers) as a primary key since after one year for a single ETIN there will be more than one entry for each assessment year Is almost the answer.ETIN ...
ComputerVersteher's user avatar
1 vote

Microsoft Access is giving me network interrupted errors

Let's have a look at the individual components. Microsoft Access According to Microsoft the Office program Access does have some limitations in regards to using it on network drives: There are ...
John K. N.'s user avatar
  • 17.9k
1 vote
Accepted

MS Access M:N Relationship query: exclude records with more than the requested values

The key is learning to write queries which directly answer the questions. Start with getting books with only one author ("and nobody else"). This will require an aggregate query which counts the ...
C Perkins's user avatar
  • 477
1 vote
Accepted

Compare all attributes in M:N relationship

Well, after some experiments and close reading of multiple SQL tutorials, I found this is a quite good solution: SELECT DISTINCTROW book_id, title, year_of_publication FROM ((book INNER JOIN (SELECT *...
MightyRavendark's user avatar
1 vote

MSAccess: creating a new row in a related table and linking it back to the "master" table

Your question is legitimate and there are some reasonable patterns that you can follow to get it working. However, I also feel like your question demands some comments regarding Access forms and ...
C Perkins's user avatar
  • 477
1 vote

How to query for something like "last active" per column in Access

I would have.... | Hostname | Timestamp | Status | |----------|-----------|--------| | Host1 | 19.02.2017| up | | Host2 | 19.02.2017| down | | Host3 | 19.01.2017| down | Then you ...
MGaddict's user avatar
1 vote
Accepted

Relating two fields of one table to one field of another

Use view designer, add Table2, then add Table1 two times. Select Table1.ID and drag till Table2.App and do the same with Table1_1 and Table2.Dependency. SELECT Table2.Id, Table2.App, ...
McNets's user avatar
  • 23.8k
1 vote

Access 2010-2016 Compatibility Error

First: - Check for "Missing:Reference" in the Visual Basic Editor. Typically it's MS Access inheriting the new references and not reverting them or making them backwards compatible. Second: - Could ...
Chris Koch's user avatar
1 vote

Query showing a number instead of the column value

A lookup table with a single value (StateAbbr) should contain two fields: StateId (Primary Key) as an autonumber type, and StateAbbr as a text type. Your Persons table should contain a number type ...
rattler418's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible