XBase programmer needs help understanding Access

On Sun, 06 May 2007 07:34:53 -0500, Tom McNally
wrote:

Thanks for your post.

What we do is this:

We have very large data files that are extracted from our accounting
system, 100,000 records and higher. Many times we have 2 or more of
these to work with at a time. Our MDBs are already at 670M for one
month's analysis, and they are growing.

The tables need some manipulation in order to bring in regional and
district data so that we can select based on this regional and
district data. After we have selected the region or district records
that we want, and we export this into Excel. Then we do pivot tables
and other manipulations to analyze the data. The reason we use pivot
tables is that we can hire analysts with Excel knowledge, but it is
hard to find Access savvy analysts.

I see two opportunities in our present process for improvement.

First: the current selections are done with queries that are
excruciatingly too time consuming. So I need to find a way to reduce
the amount of time it takes to "relate" regional and district data and
do a selection on these. We do this with joins currently.

Second, the work we do in Excel with pivot tables is simply sorts and
subtotals that could be done in Access. They are the same every time
we do the analysis, and I worry that manual processes, which Excel is,
are open to human error. Cut and paste one too few rows or one too
many rows and you have an inaccurate result.

So I am hoping to put this all into an automated process.

Our IT department is not comfortable with my doing this in Foxpro,
since they don't know it.

Thus I have to do everything in Access.

Some of the analysis is already being done in Access, but as the size
of our tables becomes bigger, the slower Access gets. As I said, we
are using joints to relate the tables and select the records, and it
takes too long to do this.

Thus I need to find a quicker way to get data into the tables, relate
them, select based on the relationships, and then report on it.

Any thoughts on this process would be greatly appreciated.
Best regards,

JAF
http://www.discountdrivingschool.com

Hi.

Can someone give me a small sample of the code for fast indexing and
relational work?

Sure. However, we can't see your database and don't know what you've
already done, but it sounds like you only imported the tables and didn't
bother taking any of the other steps required for a relational database,
such as set up primary keys and foreign keys, and possibly add indexes to
other columns used in joins, sorts and query criteria to make queries run
faster. Therefore, I'll list all of the steps you _should_ have taken, and
you can skip the ones that you've already done.

We have such big files, and it takes forever to get anything done.

That's where you'll see the huge difference between a beginner and someone
experienced with relational databases, and particularly with the database
tool he's using.

Honestly, it's a lot easier for a beginner to take these steps using the
GUI, but if you prefer code, then that's easy enough. In this example, the
two tables are named PERSON and ZIPCODE. When the step calls for you to
create a query, this is what you'll do:

1.) In the Database Window, ensure no item is selected, or at least no
table or SELECT query is selected.

2.) Select the Insert - Query menu, then select the "OK" button to display
the "Show Table" dialog window. Select the "Close" button, because you
aren't going to use the GUI.

3.) Select the View - SQL View menu to display the SQL View pane. This is
where you will paste the SQL code examples or type your own queries.

4.) Whatever is shown in the SQL View pane ("SELECT;") needs to be deleted
or overwritten by whatever you write or paste into the SQL View pane.
Essentially, you need to start with a blank slate for your query, but Access
tries to help you. If you're pasting in SQL code, that SQL code is all that
you need, not Access's "help."

5.) After you paste the SQL code or write your query, you will need to run
the query. Close the table that is going to be used in the query if it's
DDL (data definition language), or change the table's view from Design View
to Datasheet View if it DML (data manipulation language). Yes, I'll warn
you if it's going to be DDL in the following instructions.

6.) To run the query, ensure that the query you want to run is the active
window (the Title Bar of the window on top should give you a clue), and then
select the toolbar button marked with an exclamation point. (It's red in
Access 2003). It has a tool tip "Run" to help you identify it.

If you want to compare a "before" and "after" on a query with joined tables,
then create a new query and paste the following SQL code into the SQL View
pane:

SELECT PERSON.*, ZIPCODE.City, ZIPCODE.State
FROM PERSON INNER JOIN ZIPCODE ON PERSON.Zip = ZIPCODE.Zip
WHERE (ZIPCODE.State = 'Texas');

Replace Texas with any state you want that's listed in your table
(preferably with lots of records), then save this query. Get out your stop
watch, then run the query to see how much time it takes. Write down the
time, so you can compare it in a moment.

A. Primary Keys

Now, after importing the tables, a primary key should be assigned to each
table so that the database engine can uniquely identify each record in the
table. A side benefit is that the primary key will automatically be
indexed.

Did you add a primary key on the ID column in the PERSON table and the ZIP
column in the ZIPCODE table? If not, create a new query and paste the
following SQL code into the SQL View pane:

ALTER TABLE PERSON
ADD CONSTRAINT PrimaryKey PRIMARY KEY (ID);

This is DDL SQL, so ensure that the PERSON table is closed, then run the
query. Next, create a new query and paste the following SQL code into the
SQL View pane:

ALTER TABLE ZIPCODE
ADD CONSTRAINT PrimaryKey PRIMARY KEY (ZIP);

This is DDL SQL, so ensure that the ZIPCODE table is closed, then run the
query.

B. Relationships

The PERSON and ZIPCODE code tables are related, so if you don't want to set
the relationship in the Relationship window, the create a new query and
paste the following SQL code into the SQL View pane:

ALTER TABLE PERSON
ADD CONSTRAINT tblPersonZipCode_FK
FOREIGN KEY (ZIP) REFERENCES ZIPCODE ZIP;

This is DDL SQL, so ensure that the PERSON and ZIPCODE tables are closed,
then run the query. A side benefit to this constraint is that Jet will
automatically place a hidden index on the related column (because
referential integrity is enforced), so you don't need to add an index to the
column.

You can save these queries, but honestly, you'll probably never need them
again (unless you drop these constraints later and want to redo them), so
you don't need to save them. However, if you want an example for later
reference, you should save these queries.

C. Create a Query With a Join

Next, save your work and compact the database. (Select the Tools -
Database Utilities - Compact and Repair Database... menu.) If you didn't
create a query with a join on the two tables earlier, then create it now
with the SQL code I gave you above, save it, get out your stop watch, then
run the query to see how much time it takes.

It's a lot faster than without primary keys and indexes, huh?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"JAF" wrote in message
...
Thanks for both of your posts.

Can someone give me a small sample of the code for fast indexing and
relational work?

We have such big files, and it takes forever to get anything done.

Thanks.
Best regards,

JAF
http://www.discountdrivingschool.com

Hi.

We have very large data files

Very large data files are in the terabyte range. Yours isn't even in the
gigbyte range yet. This is subjective, but most database experts would tell
you that your database is small if it fits on one CD without even compacting
it and compressing it first.

Our MDBs are already at 670M for one
month's analysis, and they are growing.

Whoa! Red flag! Have you compacted the database? If so, and it's still
670 MB, then you will run out of space in a Jet 4.0 database file within
another two months. Jet 4.0 only holds up to 2 GB of data. With a more
optimized database design, you can stretch that to six or twelve months
total from the start, but you will need to migrate this data to a
client/server database with a much higher capacity, such as SQL Server or
Oracle.

However, I suspect there's no reason to panic, because you most likely
merely need to compact the database periodically and use a more optimized
database design. I would suggest hiring an expert Access developer to fix
things up and give further suggestions on the future of this database
application, because migrating to a client/server database if you don't
really need to will cost a _lot_.

The reason we use pivot
tables is that we can hire analysts with Excel knowledge, but it is
hard to find Access savvy analysts.

The main reason it's so hard to find Access-savvy analysts is that most
companies want to pay Access experts on the same pay scale as someone who
can only build an Access database application with the wizards, and doesn't
know a lick about relational database design, software development, nor how
to code in VBA. In other words, someone with world-class skills and many
years of experience gets paid about the same as a beginner with one day's
training or experience. Not too many folks are willing to put forth the
time and effort to reach that level if they get paid the same as folks who
put almost no effort into it.

the current selections are done with queries that are
excruciatingly too time consuming. So I need to find a way to reduce
the amount of time it takes to "relate" regional and district data and
do a selection on these. We do this with joins currently.

Joins are the only way to run a query with more than one table as the data
source. You need to divorce yourself of the belief that joins are
time-consuming. It's poorly designed table structures and queries, along
with database bloating, that make the query run slowly.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"JAF" wrote in message
...
On Sun, 06 May 2007 07:34:53 -0500, Tom McNally
wrote:

Thanks for your post.

What we do is this:

We have very large data files that are extracted from our accounting
system, 100,000 records and higher. Many times we have 2 or more of
these to work with at a time. Our MDBs are already at 670M for one
month's analysis, and they are growing.

The tables need some manipulation in order to bring in regional and
district data so that we can select based on this regional and
district data. After we have selected the region or district records
that we want, and we export this into Excel. Then we do pivot tables
and other manipulations to analyze the data. The reason we use pivot
tables is that we can hire analysts with Excel knowledge, but it is
hard to find Access savvy analysts.

I see two opportunities in our present process for improvement.

First: the current selections are done with queries that are
excruciatingly too time consuming. So I need to find a way to reduce
the amount of time it takes to "relate" regional and district data and
do a selection on these. We do this with joins currently.

Second, the work we do in Excel with pivot tables is simply sorts and
subtotals that could be done in Access. They are the same every time
we do the analysis, and I worry that manual processes, which Excel is,
are open to human error. Cut and paste one too few rows or one too
many rows and you have an inaccurate result.

So I am hoping to put this all into an automated process.

Our IT department is not comfortable with my doing this in Foxpro,
since they don't know it.

Thus I have to do everything in Access.

Some of the analysis is already being done in Access, but as the size
of our tables becomes bigger, the slower Access gets. As I said, we
are using joints to relate the tables and select the records, and it
takes too long to do this.

Thus I need to find a quicker way to get data into the tables, relate
them, select based on the relationships, and then report on it.

Any thoughts on this process would be greatly appreciated.
Best regards,

JAF
http://www.discountdrivingschool.com

Hi.

Also, if you want to add an index to a table on certain columns to make
sorting on those certain columns faster or queries with criteria on those
certain columns run faster, then the SQL syntax is the following:

CREATE INDEX Name_IDX
ON PERSON (LastName, FirstName);

To create a unique index on two columns:

CREATE UNIQUE INDEX Name_UQ
ON PERSON (LastName, FirstName);

Or to create an index on a single column:

CREATE INDEX LastName_IDX
ON PERSON (LastName);

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"'69 Camaro" AM wrote in
message ...
Hi.

Can someone give me a small sample of the code for fast indexing and
relational work?

Sure. However, we can't see your database and don't know what you've
already done, but it sounds like you only imported the tables and didn't
bother taking any of the other steps required for a relational database,
such as set up primary keys and foreign keys, and possibly add indexes to
other columns used in joins, sorts and query criteria to make queries run
faster. Therefore, I'll list all of the steps you _should_ have taken,
and you can skip the ones that you've already done.

We have such big files, and it takes forever to get anything done.

That's where you'll see the huge difference between a beginner and someone
experienced with relational databases, and particularly with the database
tool he's using.

Honestly, it's a lot easier for a beginner to take these steps using the
GUI, but if you prefer code, then that's easy enough. In this example,
the two tables are named PERSON and ZIPCODE. When the step calls for you
to create a query, this is what you'll do:

1.) In the Database Window, ensure no item is selected, or at least no
table or SELECT query is selected.

2.) Select the Insert - Query menu, then select the "OK" button to
display the "Show Table" dialog window. Select the "Close" button,
because you aren't going to use the GUI.

3.) Select the View - SQL View menu to display the SQL View pane. This
is where you will paste the SQL code examples or type your own queries.

4.) Whatever is shown in the SQL View pane ("SELECT;") needs to be
deleted or overwritten by whatever you write or paste into the SQL View
pane. Essentially, you need to start with a blank slate for your query,
but Access tries to help you. If you're pasting in SQL code, that SQL
code is all that you need, not Access's "help."

5.) After you paste the SQL code or write your query, you will need to
run the query. Close the table that is going to be used in the query if
it's DDL (data definition language), or change the table's view from
Design View to Datasheet View if it DML (data manipulation language).
Yes, I'll warn you if it's going to be DDL in the following instructions.

6.) To run the query, ensure that the query you want to run is the active
window (the Title Bar of the window on top should give you a clue), and
then select the toolbar button marked with an exclamation point. (It's
red in Access 2003). It has a tool tip "Run" to help you identify it.

If you want to compare a "before" and "after" on a query with joined
tables, then create a new query and paste the following SQL code into the
SQL View pane:

SELECT PERSON.*, ZIPCODE.City, ZIPCODE.State
FROM PERSON INNER JOIN ZIPCODE ON PERSON.Zip = ZIPCODE.Zip
WHERE (ZIPCODE.State = 'Texas');

Replace Texas with any state you want that's listed in your table
(preferably with lots of records), then save this query. Get out your
stop watch, then run the query to see how much time it takes. Write down
the time, so you can compare it in a moment.

A. Primary Keys

Now, after importing the tables, a primary key should be assigned to each
table so that the database engine can uniquely identify each record in the
table. A side benefit is that the primary key will automatically be
indexed.

Did you add a primary key on the ID column in the PERSON table and the ZIP
column in the ZIPCODE table? If not, create a new query and paste the
following SQL code into the SQL View pane:

ALTER TABLE PERSON
ADD CONSTRAINT PrimaryKey PRIMARY KEY (ID);

This is DDL SQL, so ensure that the PERSON table is closed, then run the
query. Next, create a new query and paste the following SQL code into the
SQL View pane:

ALTER TABLE ZIPCODE
ADD CONSTRAINT PrimaryKey PRIMARY KEY (ZIP);

This is DDL SQL, so ensure that the ZIPCODE table is closed, then run the
query.

B. Relationships

The PERSON and ZIPCODE code tables are related, so if you don't want to
set the relationship in the Relationship window, the create a new query
and paste the following SQL code into the SQL View pane:

ALTER TABLE PERSON
ADD CONSTRAINT tblPersonZipCode_FK
FOREIGN KEY (ZIP) REFERENCES ZIPCODE ZIP;

This is DDL SQL, so ensure that the PERSON and ZIPCODE tables are closed,
then run the query. A side benefit to this constraint is that Jet will
automatically place a hidden index on the related column (because
referential integrity is enforced), so you don't need to add an index to
the column.

You can save these queries, but honestly, you'll probably never need them
again (unless you drop these constraints later and want to redo them), so
you don't need to save them. However, if you want an example for later
reference, you should save these queries.

C. Create a Query With a Join

Next, save your work and compact the database. (Select the Tools -
Database Utilities - Compact and Repair Database... menu.) If you didn't
create a query with a join on the two tables earlier, then create it now
with the SQL code I gave you above, save it, get out your stop watch, then
run the query to see how much time it takes.

It's a lot faster than without primary keys and indexes, huh?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"JAF" wrote in message
...
Thanks for both of your posts.

Can someone give me a small sample of the code for fast indexing and
relational work?

We have such big files, and it takes forever to get anything done.

Thanks.
Best regards,

JAF
http://www.discountdrivingschool.com

JAF wrote in
:

A comparison: it sounds like I need to learn the joins, I have no
choice. My employer wants me to use Access. But with Foxpro, if
you ran the code I gave you earlier, not much is processsed until
you ask for the necessary fields in related files. With Access,
you have to process the query before you can use the related
files.

This makes no sense to me at all.

If you have two tables in your Access MDB and you want to display
information from both, you create a query, join the tables on the
appropriate field and then save the query. Each time you need to
view the two tables joined, you use that saved query. How is that
more work than programming the same thing?

Secondly, if the join is slow, then you haven't defined appropriate
indexes on your Access tables. Once you do so, the join should be
extremely fast, unless you're dealing with millions of records.

So, it seems to me that you just aren't doing it right.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

JAF wrote in
:

We have very large data files that are extracted from our
accounting system, 100,000 records and higher.

That's not a very big table, in my opinion. I have clients with that
many records in use all the time, running reports against it, etc.
The key is correct structuring of the tables and proper indexing.

Many times we have 2 or more of
these to work with at a time. Our MDBs are already at 670M for
one month's analysis, and they are growing.

Are you compacting on a regular basis?

The tables need some manipulation in order to bring in regional
and district data so that we can select based on this regional and
district data.

Are the tables properly normalized? If not, that could be a major
source of bloat and problems with indexing. That is, if you don't
have a properly normalized structure, your table may not offer you a
field to index that will allow a proper join with other tables.

Also, you need indexes for sorts not just for joins.

After we have selected the region or district records
that we want, and we export this into Excel. Then we do pivot
tables and other manipulations to analyze the data. The reason we
use pivot tables is that we can hire analysts with Excel
knowledge, but it is hard to find Access savvy analysts.

Why export the data to Excel? Excel can use Access data directly.

I see two opportunities in our present process for improvement.

First: the current selections are done with queries that are
excruciatingly too time consuming. So I need to find a way to
reduce the amount of time it takes to "relate" regional and
district data and do a selection on these. We do this with joins
currently.

*What* takes too long? The running of the query? If that's the
problem, then it means you haven't created appropriate indexes when
you imported the data.

If you're saying that *creating* the queries takes too long, then I
think you're nuts! Writing a query to join two tables takes about 60
seconds at most. Once you save it you don't have to do anything but
open it, change your criteria (if needed) and run it again.

Second, the work we do in Excel with pivot tables is simply sorts
and subtotals that could be done in Access. They are the same
every time we do the analysis, and I worry that manual processes,
which Excel is, are open to human error. Cut and paste one too
few rows or one too many rows and you have an inaccurate result.

Yes, sounds like it could be easily done in Access. Sorts and
subtotals are very easy in Access reports, not so easy in forms.

So I am hoping to put this all into an automated process.

Our IT department is not comfortable with my doing this in Foxpro,
since they don't know it.

Thus I have to do everything in Access.

Sounds like something that would be very trivial for Access, and
possibly a good learning project for you.

Some of the analysis is already being done in Access, but as the
size of our tables becomes bigger, the slower Access gets. As I
said, we are using joints to relate the tables and select the
records, and it takes too long to do this.

It seems to me that you've obviously not added appropriate indexes.

Thus I need to find a quicker way to get data into the tables,
relate them, select based on the relationships, and then report on
it.

I think the introduction of the term "relationships" into this
discussion was a mistake. A "relationship" in Access is a permanent
property of two tables, where the values in a field of one table are
related to values drawn from another table. In the context in which
you are working, I would say that you don't even need any
relationships -- all you need is joins. You *do* need the
appropriate indexes on the tables, but that's a matter of creating
them when you import the tables (or using the same table and
importing each data dump into the same table, with pre-existing
indexes on the appropriate fields).

The key thing that I would guess you are missing is proper indexing.
That would seem to me to explain all the performance problems you
are experiencing, which I have never seen in datasets of only a few
100K records.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"'69 Camaro" AM
wrote in :

Honestly, it's a lot easier for a beginner to take these steps
using the GUI, but if you prefer code, then that's easy enough.

I wuold say that learning Access with the steps you've just
suggested is going completely against the basic philosophy of the
whole design of Access. In other words, your instructions will get
the job done, but won't really help a FoxPro user understand the
basic ideas behind the design of Access.

I've been making a living as an Access developer since 1996 have
never executed a single DDL statement. Not even once.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"JAF" wrote in message
...

My experience, as little as it is, with Access is that joins of this
size take a lot of time to do.

Why would join data be any slower (or faster) in FoxPro?

Let's digress for a second and then come back to this point. It is
not a time consuming task to set up a relation in Foxpro. You do have
to do it in code, but you can save the code, and run the saved code
very quickly.

Right, but you telling me that you have to use developer and programming
time to set this up?


But to return to the point, I can set up a relation, and Foxpro does
not need to join in order for me to use the relation.

Well, actually, you DO NOT need to setup a relation in ms-access. You simply
use the query builder, build your query and drop in the 2nd table..and join
the data. You don't have to go into the table design mode and setup
relationships AHEAD of time. So, really, in Fox, you write code, and in
ms-access you use the query builder (and do not have to write code).

I not sure if you thinking that you somehow have to setup the relationships
ahead of time...you do NOT have to at all!!!


The query builder is NOT dependent in anyway on the relationships you setup.
You can join two tables and any same "type" field...and this join is no some
special "ahead" of time setup.

So, what you having a problem here is that you expecting us to write code,
build software when the query builder lets I do this graphically, and
interactivity, and WITHOUT having to write code.

You can most certainly use in-line sql in your code to do this join also.
You seem to be hinting that it is some better, less work, and preferable to
start writing software to do a relational join? (I defiantly missing
something here).

A comparison: it sounds like I need to learn the joins, I have no
choice.

And, you mean I have to learn code, write software to build that join in
Fox? (your kidding right!!!)

Look, the simple approach here is to use the tools you have, and to stop
thinking of some other tool. the difference between a good developer, and a
bad one is that they realize every new set of tools they learn means leaning
a new approach to how you solve problems...

ran the code I gave you earlier, not much is processsed until you ask
for the necessary fields in related files. With Access, you have to
process the query before you can use the related files.

No, not really. It depends on how your processing this data. If you sending
the data to a report, then you going to process every record anyway. if you
talking a code loop in which you process record by record, then no...you
don't have to load, or processing all records at once.

And, if you open the query, then the first page of the will only display,
and all records are not loaded.

This is matter of your context of how you need to process this data. In your
postal code example, you not really explain when, or how you need to process
this data.

Really, build the join, built a report...and out goes the data..and not
written ONE line of code. In fox, you *will* have loaded every record if you
sending to the report.

You can certainly write a code processing loop in which you process this
joined data record by record, but your example does not hint of any needs
for this approach.

So, assuming you build your query, you could process the 1st 10 records
like:


dim rst as dao.recordset
dim i as interger
set rst = currentdb.OpenrecrodSet("myQuery")

for i = 1 to 10
debug.print rst!PersonName, rst!Postalcode
next i

And, if you don't even want to build the query, then go:

dim rst as dao.recordset
dim i as interger
dim strSql as string

strSql = "SELECT Personname, Postalcode, ZipcodeCity FROM Contacts " & _
"LEFT JOIN tblPostalCode ON Contacts.PostCode =
tblPostalcode.Postcode"

set rst = currentdb.OpenreocrdSet(strSql)

do while rst.Eof = false
debug.print rst!PersonName,rst!ZipcodeCity
rst.MoveNext
loop

So, you can write looping code, but why on earth would you? There is not
need, and the savings in the developer time can be used to feed the poor.

I spent a number of years in FoxPro, and now ms-access. You just need a mind
set change. I used the last foxpro "dos" version, and a bit of the first
windows version. I prefer ms-access hands down, and I actually tend to have
*strong* coding approaches in ms-access. So, you can write looping code for
most stuff, but you will tend use the "looping" approach less when you have
sql at your disposal. The "replace with" type commands in foxpro are
replaced with sql commands in ms-access. You can also use looping code, but
then again..you don't need to.

Being to think in terms of sets, and sql. If you take the time to learn sql,
then you don't need to define those relationships in the table design.
However, for parent/child relationships, you WANT to setup relationships so
that deleting the parent record will delete the child record for you (and,
you will not be able to add a child record without first having a parent
record -- however, these *enforced* relationships are NOT needed for your
example type join, and DO NOT need to planned (setup) ahead of time.

Further, learning sql is the *best* thing you can learn. In fact I learned
sql in foxpro, and 16 years later sql is the ONLY skill set from back then I
use. (it was also the most valuable thing I learned, and I have FoxPro to
thank for that!!!).

Learning sql means you can transfer that skill to any major database system
you learn...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Hi, David.

I wuold say that learning Access with the steps you've just
suggested is going completely against the basic philosophy of the
whole design of Access.

Primary keys, foreign keys, and indexes aren't against the basic philosophy
of a relational database, which includes Access. I agree that the GUI
interface is soooo much easier to learn with, but his question, "Can someone
give me a small sample of the code for fast indexing and relational work?"
indicates that he wants the equivalent code for the commands he's used to in
FoxPro, not dragging, pointing, and clicking on items in the windows in
Access.

In other words, your instructions will get
the job done, but won't really help a FoxPro user understand the
basic ideas behind the design of Access.

I and most other Oracle professionals (at least those of us with formal
training) learned relational database design through SQL, not through a GUI
interface, and I suspect others who take formal courses using other
client/server databases also either learn the SQL first or else the SQL
behind the commands when working with the GUI interfaces of their database
tools, too.

I've been making a living as an Access developer since 1996 have
never executed a single DDL statement. Not even once.

I won't hold that against you. ;-) I will always have a very high respect
for your database skills, which are considerable. Most Access database
developers never have a need to use DDL, because the GUI has more than
enough tools for the job. But Jet SQL can do the job, too, and is often
more familiar to others whose backgrounds include using other relational
databases.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"David W. Fenton" wrote in message
. 1...
"'69 Camaro" AM
wrote in :

Honestly, it's a lot easier for a beginner to take these steps
using the GUI, but if you prefer code, then that's easy enough.

I wuold say that learning Access with the steps you've just
suggested is going completely against the basic philosophy of the
whole design of Access. In other words, your instructions will get
the job done, but won't really help a FoxPro user understand the
basic ideas behind the design of Access.

I've been making a living as an Access developer since 1996 have
never executed a single DDL statement. Not even once.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"'69 Camaro" wrote

I agree that the GUI interface is soooo much
easier to learn with, but his question, "Can
someone give me a small sample of the code
for fast indexing and relational work?" indicates
that he wants the equivalent code for the
commands he's used to in FoxPro, not dragging,
pointing, and clicking on items in the windows in
Access.

But, as we know, one of the fastest routes to complete frustration is to
expect a different product to work, or be used, the same as some other one
with which you are familiar. Encouraging someone to try to do in code in
Access, which has easier alternatives, all the things they used to do in
code in FoxPro or dBase is "enabling" them to indulge in that fantasy which
will inevitably lead them to frustration.

Larry Linson
Microsoft Access MVP