Limit In Access

I've known access to be ass for quite some time, but I got the impression that it was at least more featureful than mysql. Having had many moments recently, I'm coming to wonder if access is actually broken at SQL support...

My current problem is this: I want to assign a (one) ticket to a pupil. currently I have

, but that updates all rows. I tried adding "LIMIT 1" to the end of the query, but when I click the "switch from query builder to query results" button, it just hilights the word "LIMIT", with no message or explanation of what isn't working.

Googling round, it seems that access doesn't support LIMIT, it uses TOP instead. However when I use TOP, the same thing happens (TOP is hilighted with no explanation)


ED> Ok...

seems to work for insertion, but it seems like very silly syntax :/


ED2> I want to show a form that shows the pupil's name, and how many commendations / penalties / (raffle) tickets / letters (of complaint sent to parents) there are for that pupil. Currently the following (generated by access) works, but it only shows pupils that have all four - I want it to show all pupils, and just "0" if there are none of the specified item - any ideas?

<rant>
A note on the usability of open source:
MS-Access: "Reserved error(-4517); there is no message for this error"
MySQL: "ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause"

People keep saying linux needs an access equivalent, but WTF? Given the choice between a real database and this, I'd vote to *avoid* an access equivalent...
</rant>

This post has been edited by Shish on Oct 30 2004, 11:08 AM

You need to use outer joins for that, rather than the inner joins you're currently using. If you're not familiar with outer joins, I'm sure you can find some helpful articles with Google.

that would be wrong. i would only use access if people were shoveing glass rods up my dick and then breaking them....

as for you main problem, like Wiz says use outer joins (left outer join in your case), you would have the same issue no matter what dbs you used.

also quit depending on access to generate your sql for you, even someone like me can learn sql, so can you

I've actually done lots of SQL, just not MS-SQL; and MS-SQL seems different to everyone else's, just like all their other standards >:(

There's only a week until this demo project has to be handed in, but I'm planning on making a LAMP LiveCD for my final. Writing a PHP front end from scratch and setting up the database with the sql command line is WAY easier than using access wizards...

Assuming you have a primary key on the table (you damn well should), you can do this:

Sorry, no. Access may be marginally more featureful than, say, Paradox, but it's not a wart on the nethermost parts of MySQL. (Yes, I know there are much more powerful databases than MySQL. I have worked with several. Access is not one of them.)

You're right about Access SQL being different from everyone else's SQL, though...

Access+JET (which is what I assume you are using) is barely one step above "Cardfile" (remember that?) - where it CAN come in use is as a front-end if you switch out the backend.

Besides, if you venture out of the most basic SQL commands you WILL find major differences between the different databases out there - it's a very non-standard standard.