HELP...!

[Carlo]

Hi all,

Firstly, thanks for all of your help thus far.

I have having major major problems with a couple of issues. I have a table
set up, I think correctly, which records the common details (such as contact
details) of our adult users of our community centre (Adult Users Table). I
then have a table which records the extra information we need for adults who
have children i the nursery (Adult Nursery Users Table). I then have query
(Adults Users Extended) along the same lines as the 'Contacts Extended' query
which comes with the contact management template database. This has a 'File
As' filed, and 'Contact Name' field, but contains the information from both
the Adult Users Table and the Adult Nursery Users tables. I then have a form
which is

My problem is that for some reason the data in the table doesnt flow through
to the query (apparently displays no records), and so also doesnt flow throuh
to the form.

I've checked the set up of the various sections items (table, form and
query) against the business contacts section (which was taken directly from
the template), and they seem to be the same in every way that I can see. But
the latter works, and the former doesnt.

Any ideas as to what I may have overlooked?

Any help wold be much much appreciated

Carlo

 

[BruceM]

You should use something descriptive in the subject line of a newsgroup
posting. Also, proofread your post in order to avoid things such as:
"I then have a form which is ..."

Describe in a bit more detail the structure of the tables, and describe the
relationship between them. Then describe the query. It is not necessary to
include every field when you describe a table. For instance, if you have
Address information you could have something like this:

tblUsers
FirstName
LastName
Address, etc.

What do you mean by "extra information" in the NurseryUsers table?

It may be that somebody here is familiar with the template you used as a
basis for your project, but if not you cannot count on people researching
the template. That is why you need to provide a sufficiently detailed
explanation.

 

[Golfinray]

Is your form a form and subform? Do your queries display the proper data? Is
the control source of your form the query or the table? If you are using a
form and subform are they linked properly? I'm just trying to figure out what
you have here.

 

[Carlo]

Sorry about that. I had a crying baby come up to me half-way through posting
that question, hence my slight confusion.

The form is based on the query, and the query is not returning any
information. The form therefore should have information drawn from two
tables (via the query) in the main body, and also has two subforms linked to
other tables rec ording adult-child relationships and days of adult's
employemnt.

The template that I used for the main business contacts was the template
available from Microsoft online. It works fine for the business contacts,
but I have then tried to replicated those to record information on adult and
child users of the database. The information re the adults and children is
more complex, hence the subforms and multiple tables.

The tables set up is as follows:

tblAdult Users
Adult ID (PK)
Address etc
Ethnicity/DOB etc

tbl Adult Nursery Users
Adult ID (PK)
Adult Employer details
Emergency contact details

tbl Adult days of employemnt
Adult ID (CPK)
day of employement (CPK)

Then there is the query, which contains all info from tbl Adult Users and
tbl Adult Nursery users, and compiles the Adults full name, and files it as
Last name, First Name. This doesnt wok.

The Form is then supposed to access and add to the records via the query,
but cannot do so.

I've been through the settings and they are the sameas for the business
contacts as far as i can see, with the exception that because of the
subforms, i cannot set the Adult User form to 'continuous forms', only to
single form view. Could this be the difference? I have also tried, without
success, to omit the query, and use the form to access he tables directly.

Really not sure what to check or try next...

Carlo

 

[BruceM]

I get that PK is primary key, but what is CPK?

What happens when an adult user becomes a nursery user, or a former adult
nursery user is no longer a nursery user because the tyke grows up? Do you
move the names from one table to another, or what exactly? If they are
moved to another table, do they get a new PK? If so, what happens to
existing relationships? BTW, you did not describe the relationships.

It would probably be best to have a single Users table, with a yes/no field
to indicate whether the peson is an adult nursery user. This may be a
situation where it is appropriate to have a subclass table related
one-to-one to tblAdultUsers for the additonal details needed for an adult
nursery user. This is where the additional information could be stored.
However, unless there is more than a few fields it may be best just to
include the fields in tblAdultUsers.

You have not described a Children table, but there are two main approaches
that occur to me. One is that you could have a Children table that is
related to tblAdultUsers. The other is that everybody can be in one table,
including the children. To associate a parent with a child you would use a
self-join (creating a relationship within the table rather than between
tables). I sort of understand how this works, but I cannot really begin to
advise you about this. This technique is used for things like church
membership databases, where the children grow up and have families of their
own. You still want to know that somebody is the son or daughter of a
particular person, but also want to allow for them having their own children
one day.

I don't think your use of two separate tables for storing names, etc. is
going to work well, if it can work at all, but if you are having trouble
with a query it is generally necessary to post the SQL, or underlying
language. To do that, open the query in design view, then click View >>
SQL. You can copy what shows up, and paste it into a newsgroup message.
If the tables in the query are not related I think you will just get a bunch
of duplicate information. If one table contains 10 records and the other
100 records your query will return 1000 records. However, if there is a
join between the tables it could be that the nature of the join means the
query does not return any records.