Free Tutor

[Jack Marks]

I am developing an Access database, and am having some difficulty with
simple items, things that the pros instinctively know to do.

I might point out that I, previously, developed several dBASe applications,
years ago, which are still in use today, so I am pretty good with database
design but not with Access. You might say that I write (wrote) "gutter
dBASE" and I have written "gutter BASIC", all of which worked and did the
job. (By the way, I can't do any better with Visual Basic than I can do
with Access.)

Now I wish to write "gutter Access." In order to do this, I need a focused
effort on how to do one thing. I do that one thing and, it works, and
then, if necessary, move on to the next thing, until I again run into a
problem.

So as I encounter my focused questions, should I post them here, and argue
with people that no, they are not answering my focused question, and
everylbody gets upset, or is there a way I could directly communicate with
someone who

1. Can simply answer the focused question.
2. Can understand that their answer won't work for me, and we need to
re-focus on the question, back and forth.
3. Could send a test database back and forth.

 

[Brendan Reynolds]

If I think you are about to shoot yourself in the foot, I'm not going to
help you do that, not even if you were paying me. If I think your 'focussed
question' indicates that your design is flawed and you are headed down a
blind alley, I might tell you so, or I might not answer the question,
depending, among other things, on whether I think you are likely to listen
to me or not. From that point of view, you could regard the fact that
someone is prepared to argue with you about the correct approach as a
compliment - it means they think you're worth it!

I suspect that the short and direct answer to this particular question - can
you get someone to answer your questions *the way you want them answered*
and can you get that *for free* is probably no.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[RobFMS]

Jack

Here is some of my recommendations:

1. Find the appropriate skill level books at the book store that will help
you learn Access.

2. If needed, post questions to the newsgroups. Be prepared for:
1. Someone not answering your questions... it happens....
2. Someone answering your question ... and it works... Great!
3. Someone answering yoru question ... and it doesn't work ... try
asking the
question in a different fashioin

3. There are Access groups that actually meet for meetings and open
discussions.
FMS has a list
(http://www.fmsinc.com/free/links/index.html#Access User Groups),
I'm sure others do to.



HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Jack Marks]

Hmmpph. Sounds like there is no free tutor out there, so I will just have
to start with Question One, Numero Uno:

I want a simple database, with 2 tables. The first table lists Managers,
and just gives the manager's name and auto-numbers the Manager ID. The
2nd Table lists Courses, and just gives the course name and auto-numbers the
Course ID. Got it so far?

Now on my input form, I want the manager name to be selected from the drop
down of manager's names and the course name to be selected from the
dropdown of course names.

So, do I begin by writing a 3rd table to store this combined data, or do I
write a query and put a control number as an item in the query?

 

[Dirk Goldgar]

Hmmpph. Sounds like there is no free tutor out there, so I will
just have to start with Question One, Numero Uno:

I want a simple database, with 2 tables. The first table lists
Managers, and just gives the manager's name and auto-numbers the
Manager ID. The 2nd Table lists Courses, and just gives the
course name and auto-numbers the Course ID. Got it so far?

Now on my input form, I want the manager name to be selected from the
drop down of manager's names and the course name to be selected from
the dropdown of course names.

So, do I begin by writing a 3rd table to store this combined data, or
do I write a query and put a control number as an item in the query?

You must have a third table, to store the fact that Manager X is
associated in some way with Course Y. That could be a table named
ManagersCourses, with fields ManagerID and CourseID. These would both
be Number/Long Integer fields, each being the ID of the corresponding
record in the obvious related table. This table, ManagersCourses, could
have a separate primary key of its own, but most likely you would just
use the two combined fields {ManagerID, CourseID} as a compound primary
key. That would imply that you can't have two records in the table with
the same ManagerID and CourseID.

Having created this table, you can use it as the recordsource of your
input form. On that form, use a combo box to display and edit each of
the fields. One combo would be bound to the ManagerID field and would
have the Managers table as its Row Source, with two columns: ManagerID
and ManagerName. The combo's Bound Column woud be the first column, but
you would set the Column Widths so that the first column has a width of
0, so that the name is what displays in the control. The other combo
would be bound to the CourseID field and would have the Courses table as
its Row Source, with two columns: CourseID and CourseName. Again, the
Bound Column would be the first column, but the Column Widths would be
set so as to hide that column and show only the course name.

 

[Jack Marks]

Your answer looks good, and to the point. Give me a day or two to try it
out. Thanks.

 

[Jack Marks]

2 of us have tried to do what you suggested, but so far have failed. We
need more specific information. Here is where we are:

we have the 2 tables, and when we add the 3rd, that's where we get into
trouble. The 3rd has it's own auto-number, since we don't have a clue how
to make the key you suggested consisting of the combination of the manager
ID and the course ID. So we end up with a table, with the table id, the
manager id, and the course id, but there is no drop down of choices.

1. How do we know we have the table made correctly? Will what we have
work, if we go to the next step?

2. How do we use the table so we get the drop down of course name and
manager name?

Should we do this with the Wizard, or with the design your own thing?

We could really use a step by step direction.

 

[Dirk Goldgar]

2 of us have tried to do what you suggested, but so far have failed.
We need more specific information. Here is where we are:

we have the 2 tables, and when we add the 3rd, that's where we get
into trouble. The 3rd has it's own auto-number, since we don't
have a clue how to make the key you suggested consisting of the
combination of the manager ID and the course ID. So we end up
with a table, with the table id, the manager id, and the course id,
but there is no drop down of choices.

You would make the compound primary key by (in table design view)
selecting both the fields and then clicking the little "key" button on
the toolbar.

1. How do we know we have the table made correctly? Will what we
have work, if we go to the next step?

You can keep the table the way you've created it now if you want, but
I'd recommend getting rid of the autonumber key you added and just using
those two fields as the primary key.

2. How do we use the table so we get the drop down of course name
and manager name?

Although it is possible to get a table datasheet to show you dropdown
lists for its fields, I don't recommend it. In my view, people should
not usually be looking directly at tables; that's what forms are for.
If you do have to look at a table in datasheet view, it's because you're
debugging something. In that case, the table should show you what's
really stored in it, not some looked-up value.

Should we do this with the Wizard, or with the design your own thing?

We could really use a step by step direction.

I thought I gave you that already, when I wrote this:

So, create a new form in design view. Open its property sheet, and on
the Data tab, on the Record Source line, enter the name of your table.
When you do that, I think the field list for that table will
automatically be opened for you -- if it isn't, click the Field List
button to open it. Then drag all the fields from the list to the form
and drop them there.

The fields will by default be created as text boxes. Change each to a
combo box by right-clicking on it and selecting Change To -> Combo Box
from the context menu. Then edit the properties of each of the two
combo boxes as I described above.

 

[Jack Marks]

So now I have 3 tables, with
2 fields in Managers
2 fields in Courses
2 fields in Managers-Courses, which are both indexed

Am I going to bring all 3 tables into my form, or just my Managers-Courses
table? I will, of course, be adding more fields, later, to the Managers
Table and to the Courses Table.

 

[Dirk Goldgar]

So now I have 3 tables, with
2 fields in Managers
2 fields in Courses
2 fields in Managers-Courses, which are both indexed

Am I going to bring all 3 tables into my form, or just my
Managers-Courses table? I will, of course, be adding more fields,
later, to the Managers Table and to the Courses Table.

Don't name your table "Managers-Courses" -- that is, don't use the
hypen -- unless you can commit to always enclosing the table name in
square brackets ([]) every time you refer to it in code or SQL.
Otherwise whatever computer process is interpreting the name will think
you're trying to perform a subtraction. I strongly suggest you drop the
hyphen and name it just "ManagersCourses".

As I said originally, your form's Record Source, at least for now, will
be the single table ManagersCourses. That will enable you to edit that
table, but not the other two tables.

To edit the other two tables, you ought to have forms based solely on
them. If you add fields to these tables later, you can then add those
fields to the forms as well. You can also set up a version of the
ManagersCourses form as a subform on either of these two forms. If you
put it on the Managers form, and link it via the ManagerID field, then
the subform will show all the courses that are associated with the
current manager. If you put it on the Courses form, and link it via the
CourseID field, then the subform will show all the managers that are
associated with the current course.

If you're interested in setting up subforms this way, you had better
read the online help topics on creating subforms.

 

[Jack Marks]

For ease of design, I have tentatively decided, to have 3 tables, where the
first table lists the managers, the 2nd table lists the courses, and the 3rd
table gives a full line of exploded data about the course the manager is
taking, and manager info, so it will have its own index. To fill out the
table, one can add a line by hand, right into the table, pulling down detail
items for selection from the other tables, if there is another table. I
suppose one would call this "gutter Access" but it should be easy to create,
and easy to develop further, with reports, queries, etc.

If I can get this table running nicely, it appears, from my testing, to be a
simple matter to take the whole table and put it into a form, and still keep
all of the dropdowns. So that is what I am going to try now.

So now I have 3 tables, with
2 fields in Managers
2 fields in Courses
2 fields in Managers-Courses, which are both indexed

Am I going to bring all 3 tables into my form, or just my
Managers-Courses table? I will, of course, be adding more fields,
later, to the Managers Table and to the Courses Table.

Don't name your table "Managers-Courses" -- that is, don't use the
hypen -- unless you can commit to always enclosing the table name in
square brackets ([]) every time you refer to it in code or SQL.
Otherwise whatever computer process is interpreting the name will think
you're trying to perform a subtraction. I strongly suggest you drop the
hyphen and name it just "ManagersCourses".

As I said originally, your form's Record Source, at least for now, will
be the single table ManagersCourses. That will enable you to edit that
table, but not the other two tables.

To edit the other two tables, you ought to have forms based solely on
them. If you add fields to these tables later, you can then add those
fields to the forms as well. You can also set up a version of the
ManagersCourses form as a subform on either of these two forms. If you
put it on the Managers form, and link it via the ManagerID field, then
the subform will show all the courses that are associated with the
current manager. If you put it on the Courses form, and link it via the
CourseID field, then the subform will show all the managers that are
associated with the current course.

If you're interested in setting up subforms this way, you had better
read the online help topics on creating subforms.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

For ease of design, I have tentatively decided, to have 3 tables,
where the first table lists the managers, the 2nd table lists the
courses, and the 3rd table gives a full line of exploded data about
the course the manager is taking, and manager info, so it will have
its own index. To fill out the table, one can add a line by hand,
right into the table, pulling down detail items for selection from
the other tables, if there is another table. I suppose one would
call this "gutter Access" but it should be easy to create, and easy
to develop further, with reports, queries, etc.

If you're the only one using the application, there isn't anything
terribly wrong with working directly in the table datasheets, though
it's very limiting. I don't recommend using lookup fields, either, but
it's probably not going to cause you any serious problems, and is almost
required if you don't want to use forms to edit your data. "Gutter
Access" may be a good term for it; still, the flaws in it won't really
hurt you until you try to grow your database into a full-fledged
application. Good luck.

 

[Jack Marks]

See my fresh post, Links, Queries, and Forms. I am starting fresh from the
point where I have 3 tables and they are nicely linked.