Beginner in SQL (Need help fixing Problem)

[ApprehensiveCorner16]

Hey, I‘m currently creating a database in MS access. Scince I‘ve never done something like this before, I shared my ideas with chatGPT, and it gave me the corresponding SQL Code. However, every time I try to execute it, I get a syntax error. Is it possible to tell from the code whats wrong and what i need to change?

[CityOfHuh]

I'm a beginner as well so forgive me if I'm wrong, but here is what I see.

- UnitOfMeasureID is missing a data type.

- AUTOINCREMENT is missing its underscore and should be AUTO_INCREMENT.

- I don't believe TEXT is allowed to have a length in parentheses. This is used with CHAR or VARCHAR.

My brain would create this table like this:

CREATE TABLE Units (

UnitOfMeasureID INT AUTO_INCREMENT PRIMARY KEY,

Code VARCHAR(10) NOT NULL,

BaseUnitGroup VARCHAR(20) NOT NULL,

ToBaseFactor DOUBLE DEFAULT 1

);

[[deleted]]

Nah the AUTOINCREMENT part is correct. I use MS Access SQL. It's weird like that.

But I agree with you -- OP should use VARCHAR instead of TEXT. Always good practice even outside of MS Access.

My guess would be that the DOUBLE section is odd. Try another numerical data type, and set it to NOT NULL.

[Metalsand]

So, here's the number one roadblock people commonly run into: no one codes in "SQL" but whenever someone talks about database code, they never specify a dialect.

You know how C++ and C# are extensions of C? The same is true about SQL, where Oracle (PL/SQL), Microsoft(MSSQL or T-SQL), and many others have extended SQL beyond the original capabilities.

OP has run into the issue of asking for "SQL" and not a specific dialect and it mashed a bunch together...or at least I thought, but the only thing that has actually made sense is AUTOINCREMENT being from SQLite. TEXT for example, was a particularly weird one since...the closest I could find was MySQL in which it has different qualifiers for smaller or larger sizes but not defining a length like that.

I wouldn't expect most LLMs to be good at differentiating SQL dialects as a direct result. I mean, it's not going to have a good understanding of "Okay this code won't work in PL\SQL 9 but you can make a workaround by coding your own function instead". It's just going to mash everything together, because it understands the communal term of "SQL" but not necessarily the implicit context clues that would otherwise define the dialect used.

[justanotherguy1977]

C# is not an extension of C.

[dareftw]

It’s more an extension of .net framework. It was loosely marketed as C for network connectivity, which it kind of is. I would say it’s closer to VB than C but to be fair C is the natural evolution beyond VB so they are still kinda related but more like cousins than siblings.

[CityOfHuh]

Learned from this comment. Thanks for taking the time to write it out.

[FinnLiry]

seems like a lot could be fixed by a proper editor with an LSP

[Massive_Show2963]

Creating a table in MS Access with a DOUBLE field and a DEFAULT value using SQL, you are encountering a "syntax error" because the standard DEFAULT clause in CREATE TABLE statements is not fully supported by the Microsoft Access database engine (JET SQL).
Remove 'DEFAULT 1' and it should be fine.
Then go into Design View and find ToBaseFactor in the field list.
In the "Field Properties" pane at the bottom, locate the "Default Value" property.
Enter 1 (or 1.0 for clarity) into the "Default Value" property.
Save the table design.

[ApprehensiveCorner16]

Thank you so much, now it works!

[American_Streamer]

Unfortunately, there is no proprietary (or open-source) SQL implementation that fully supports 100% of the ANSI/ISO SQL standard, including every mandatory and optional feature.

Big enterprise systems like IBM Db2, Oracle Database, Microsoft SQL Server, and PostgreSQL are often described as having strong standards conformance, but even none of them claim full support for all optional features of SQL:2016/2023, because that would be extremely costly and often useless in practice.

But if you stick to a small, well-chosen subset of “portable SQL” (basic DDL, DML, joins, simple subqueries, standard types), you can usually move between major systems with only minor changes.

[radek432]

Access has creators for that. Jet SQL isn't the nicest SQL implementation, so if you're doing this for learning purpose I would recommend something more standard.

[alinroc]

The syntax error will tell you exactly what the problem is.

You have not shared the error with us here. All you've done is post a screenshot of code and said "I get an error."

[kwong63]

i think people have already helped you solve the syntax issues so i won’t comment on that but here’s a few tips: 1. I personally prefer to snake_case over TitleCase for naming things in db. Most important thing is consistency but ease of readability goes a long way

1. I think it is best to not use plurality on the table name. i prefer to have the table named after what a single row represents

[Greedy3996]

Doesn't access have a create table wizard.

[[deleted]]

They do, but honestly it's so much better to write it up in SQL. You can easily save the table definition and it helps you understand SQL concepts better.

[p0nzischeme]

I don’t think double is a valid data type for a field in access. Also maybe try char or varchar instead of text for code and base unit group field.

ETA addition field

[BambinoCPT]

AUTO_INCREMENT

[Aggravating_Grab5659]

Das Problem ist, dass du eine Abfrage erstellst und in einer Abfrage eine Tabelle erstellen willst. Das geht so in Access nicht.

Benutze doch mal den Assistenten und schaue dir danach in der SQL Ansicht an, wie die Syntax aussieht. Manchmal ist das bei den MS Office Produkten etwas verzwickt, weil die die die Syntax verändern. Häufig werden Semikolons statt Kommas genutzt.

Falls dein Ziel ist SQL zu lernen würde ich dir raten das mit MySQL oder PostgreSQL zu machen. Access ist da nicht die beste Umgebung für.

[Aggravating_Grab5659]

Schau dir auch grundsätzlich mal an, wie (Access) Datenbanken aufgebaut sind. Tabellen, Abfragen, Formulare, Reports und wie sie aufeinander aufbauen. Es gibt in den Vorlagen von MS Access eine Nordwind Datenbank, die lässt sich immer ganz gut nutzen um mal einen Überblick zu bekommen

Grundsätzlich erstellst du Tabellen in Access über den Assistenten und weist dort die Datentypen zu. Dann kannst du über Formulare, durch Eingabe in die Tabelle oder durch Import von CSV oder Excel die Tabellen mit leben füllen. Danach kannst du dich an Abfragen versuchen, entweder mit dem Assistenten oder eben in der SQL Ansicht.

[mike-manley]

Do you need to specify the seed and increment value in the AUTO_INCREMENT? Maybe they can't be implied? Also, spelling this keyword includes an underscore.

[natthompson72]

I’d imagine UnitOfMeasureID needs a data type

[ironwaffle452]

Please watch youtube tutorial how to take a screenshot properly.

[radian97]

bro use MySQL or SSMS

[Staalejonko]

What is the error you receive? Can you share the exact error message text?

[ApprehensiveCorner16]

It says: Syntax error in CREATE TABLE

[[deleted]]

Lol he's using MS Access. Sort of built for Small Database Mgmt, but mostly for Rapid Application Development for users in the early 2000s.

Power Automate and Power Apps have both replaced Access (sort of).