anyone know an sql syntax link?

anyone know of a link to simple explanation of the syntax for sql?
like how to count certain records etc

In the present case I'm trying to find if there is an sql statement
that would function in place of the little pseudocode snippet below...?

given:
two tables

tblLength
two fields
Field1: Length(number) (not unique)
Field2: Shape(text) (not unique)

tblShape
two fields
Field1: Shape(text) (unique)
Field2: Area(number) (not unique)

non sql vb collection idea of a way to count items
Pseudocode basic idea
use a collection or ? for every shape in tblShape
increment a list of the varying lengths for that shape
count occurances of each length

if oRs is a recordset stepping through a loop or returned recordset from
tblShape
Query:
colCount is a collection - first item = Lengthvalue, second item = count
colCounts is a collection of colCount collections
oRs2 is a recordset stepping through tblLength

For Each oRs in tblShape (or while not ors.eof )
curShape = Field(Shape).Value
For Each Field(Length) in tblLength (or while not rs2.EOF)
curLength = Field(length)
If Field(Shape) = curShape then
For Each colCOunt in colCounts
If colCount(1) = curLength then
colcount(2)=colcount(2) + 1
bFoundCount = True
End if
Next colCount
If Not bFoundCount then
Set colNext = New collection
colNext(1) = curLength
colNext(2) = 1
colCOunts.AddItem colNext
'etc - add colCount for each diff length val
End if
End if
Next Field(Length)
Next Field(Shape)

or something like that, to arrive at something like:

Report for Shape "A"
2 @ 24
3 @ 12
5 @ 48
etc

imaginary sql guess
Select Field(Length) from tblLength, where Field(Shape) = Field(Shape) from
tblShape, count(*) or something - order by whatever
now that certainly doesn't work nor do several variations of the above
But I figured a basic walk through the sql syntax would quickly show the
way.
I just haven't found it searching yet.


and for extra credit points

question two
how to use values from two tables
Calc volume:
Length(from tblLength) * Area(from tblArea)= volume for this entry

Tia
Mark

 

Supossing that there will be only one area value for each shape, IMHO the
tables should be

Table "Shape"
ID (autonumber, key)
Shape
Area

Table "Length"
ID (autonumber,key)
IDshape
Length

Then, the SQL to get the lengths for each shape would be

SELECT SH.Shape,LG.length,count(LG.length) FROM Shape SH LEFT JOIN Length LG
ON SH.ID = LG.IDshape GROUP BY SH.SHAPE,LG.LENGTH

doing a debug.print of the recorset you should get something like this:
Shape length count
A 12 2
A 14 3
A 44 1
B 15 3
B 24 5
B 50 2
C 12 8
C 30 3

 

http://www.aspfaq.com has some excellent, concise solutions to common
problems. It's not really SQL specific, but alot of good info for anyone
dealing with database access.

Also, the ww3 schools (sorry, I don't have a link handy) has some very good
beginner type tutorials.

 

Thanks,
Yeah, since this is the first time I've tried to look at ado, I completely
forgot acad had a group specially for that.
Thanks for the reminder.
Mark

 

Thanks LochDhu,

guess not!

yep, created db in access, intend to read via vb calls to adodb objects

I'll check this out tonight after work.
I don't have access on this machine but I think I can try to read the fields
with some vb subs i'm working on.

Thanks for the links too, That's what I really need to look at to get the
basics of how an sql string is formed
I've seen several and have a vague idea of some of the keywords but the
basic form still eludes me when I want to make a complicated(for me) search,

Thanks again for your time. This will be very helpful to speed my learning
curve.

Mark

 

Gracias Amigo,
Yo necesito estudiar este bien.
Este es exactamente que busque.
Muchas Gracias y pardon para mi Espanol.
Mark

Does this mean tblShape is named SH?

SELECT SH.Shape,LG.length,count(LG.length) FROM Shape SH LEFT JOIN Length
LG
ON SH.ID = LG.IDshape GROUP BY SH.SHAPE,LG.LENGTH

so TableName.FieldName is syntax to get a certain field?
I thought I had to do TableName.Field("FieldName")

I'm going to look at some links LochDhu posted, cause I really don't
understand the whole format of an sql string yet.
some of it is obvious but how things get combined and filtered is what I
need to get a grip on the basics of.
Thanks Again,
Mark

 

Thanks Tom,
I'll check these out too!
Mark

 

Does this mean tblShape is named SH?
Yes, it's an on the fly alias

And BTW, this is a simple way to get what you're looking for.
You should look at the SHAPE command, to get a hierarchical recordset.

 

In addition to the previous suggestions, there are apps that build the
syntax for you. You can then learn sql from those examples. If you have MS
Access, you can build a query in the query designer and then switch to sql
view to see what was generated. I often use that to copy/paste to a code
module where I'm building a complex sql statement in code. It then takes
minor editing for variable names, quotes, etc. to get it into a vba string.
Plus I know it works since I can test it in Access.

 

ah!
and if Shape is a 'keyword' or whatever it should be called - in sql - I'd
better stay away from using that for Field name eh?

Thanks again
Mark Propst

ps
mi esposa y yo escuchamos que Costa Rica es una paiz muy buena para vivir.
Es verdad?
(pero posiblamente ustedes no quieren que mas gringos venir?)

 

cool,
I'll check that out too. I don't have access here but I do at home so I'll
try when I get back to that station.
Have never worked with dbs or access before so it's all new territory for
me.
I'm sure once I understand a bit of the basics it won't be so mysterious as
it is now.
Thanks
Mark Propst

 

ah!

Yes, that is a good idea.


ps.
And about Costa Rica, what can I tell you ?
We're happy here ! Weather in San Jose is between warm and cool all year
round. Life is cheap, food is good, beaches are great and the "ticas" are
beautiful. River rafting, volcano expeditions, tropical rainforest
expeditions, exotic bird watching, bug researching, are all just a couple of
hours away.
And all gringos are welcomed ! Pura vida !

Here are a couple of links with info on Costa Rica:
http://www.lonelyplanet.com/destinations/central_america/costa_rica/
http://www.visitcostarica.com/ict/paginas/informacion.asp


--
Saludos, Ing. Jorge Jimenez, SICAD S.A., Costa Rica

 

.....will likely open a whole new set of possibilities for you. It certainly
did for me. I've probably only learned about 5% of what's possible with SQL
alone and it's amazing how much I've done with it.

You'll also find out that SQL is much more powerful than is obvious by
simple tutorials. It's incredibly simple for the amount of power it
provides in managing data.

 

That's what I'm getting the hint of. I've deduced by looking at various
posts that where I would have previously thought - to get each record from a
table, then do a bunch of code putting values into collections and sorting
or counting the collections - that by judicious sql wording I can get the
derived info directly - very cool.
Now to figure out how to do it, and then how to display it once i get the
kind of records I'm looking for.
With the links provided so far I'm sure when I get the time to look at them
much of it will become more clear.

Thanks again for your help.
Mark Propst

 

Now to figure out how to do it, and then how to display it once i get the

Use Access to create a link to your db. Build a query and examine the
results to see if it returns the records you want. Then use the sql query to
create a recordset in ADO. To display it, you can link textboxes to fields
using the tb's RecordSource property.

 

Textboxes would be easy to use.
Curious if you have used DataGrids?
I tried once but couldn't figure how to set the DataSource property -
it showed up in object browser but not property pages, nor would it work via
code.
There seem to be many datagrids of varying versions so there may be an issue
with which one to reference.
There's also something about some of them need a DataControl also in order
to function or something like
that.
I figure once I get a handle on returning the right records, then I'll go
off on the search for best ui to display results.'
Thanks as always for your input.
Mark Propst

 

http://www.w3schools.com/sql/default.asp

 

Thanks, I'll check that one too
Mark