Relationships and Tables

T

Tia

Is it possible to have 2 tables listed as subtables for a main table?

I have a Customer Table that is my main table. I then have 2 tables of
systems. One is for the summer and one is for the winter. I want these
linked to my Customer Table. I am only able to link one or the other.

Does anyone have any suggestions???
 
S

Steve Schapel

Tia,

If you are talking about defining relationships between tables in the
Relationships window, then it certainly should be possible to have
multiple tables related to your Customer table. I do not understand "I
am only able to link one or the other", can you give more details of
what you are trying wnd what is happening?

Having said this, it could very well not be a good idea to have separate
tables anyway for your summer and winter systems. Without knowing more
about your data, I can't say for sure of course. But the usual and
"normalised" way to approach this type of situation is to put all
systems in one table, with a Season field to identify each record as
either summer or winter.
 
J

John Vinson

Is it possible to have 2 tables listed as subtables for a main table?
Click to expand...

No. But you can have ten or more tables bound to Subforms on a main
Form.
I have a Customer Table that is my main table. I then have 2 tables of
systems. One is for the summer and one is for the winter. I want these
linked to my Customer Table. I am only able to link one or the other.

Does anyone have any suggestions???
Click to expand...

Never use table datasheets for data entry or editing; use Forms
instead.

If your systems tables are of the same structure - the same number of
fields of the same types - I'd suggest using just *one* table, with a
Season field.

If, however, they're very different (skis, boots, and poles in the
winter, bicycles and white-water rafts in the summer <g>) you might in
fact want two tables.

John W. Vinson[MVP]
 
T

Tia

Thanks for your time...

I tried to link the 3 tables in the relationship window. It will let me
define a relationship with both the summer and winter table, however, it will
only save the link between one table and the Customer Information table.

As to why I'm wanting to do this. The data is actually the same except for
the season. We are using the data to create reports and schedules for
technicians so it is a list of systems to be serviced. Right now, we have it
set up the way you are suggesting but when it comes time to sort the systems,
I can't get the desired outcome (i.e., systems from all seasons shows up on
reports or systems get seperated in undesireable ways)....I've tried all the
suggestions and manipulated the data in all the ways I can possibly think of.
So I'm thinking that if the data is sorted into two tables at the beginning.
I will get the desired result further down the chain because Summer reports
will read only from the summer table, etc. etc., so there will not be a way
for winter systems to be displayed on summer reports.

If you have any suggestions or ways to set this up, I will glady try
anything. Thanks!
 
S

Steve Schapel

Tia,

I would strongly recommend leaving all the data in the one table. I
promise it will be less problem in the long run. You need a field in
the table to identify each record as summer/winter, and then your
reports will simply need to use this field to select the right data.
This can be done in a number of ways. For example, you can base your
reports on a query where the "season" is used as a criteria. Or you can
use the Where Condition argument of your OpenReport method to select the
required data. Etc. I can't be more specific without knowing more
details of what you are doing, but if you got "undesireable" results
before, I am sure it is just a simple problem with the way you were
doing it.

As for the relationships, please make sure there is a unique index on
the field in the Customer table which is being used for the linking, and
also that there is *not* a unique index on the linking field in the
other tables, and that they are the correct data type. Otherwise,
please post back with the names of the tables and fields, and say which
fields you are trying to use for the relationship joins.
 
T

Tia

This is my dilemna in leaving all the data in one table. My database is
currently setup as follows: User enters System Information on the Systems
Form. On the form there is a box that is called Service. User selects
A=Annual, S=Summer, W=Winter. This information is all stored in teh System
Information table. Employees than use this information to create schedules
and reports that wil be used during the time of service. The schedules work
flawlessy, however, the reports have trouble distinguishing between the
Service (A,S,W). I think the problem comes in when Summer Service has to sort
out the A and S from the W (or vice versa with winter service). It ends up
either pulling only S or all of the Service. Neither outcome works for what
we are doing.....This is why I was thinking of dividing the System
Information table out to Summer and Winter and then having the Reports look
to the specific table for their data depending on whether it was for Summer
service or Winter Service.

I've come up with another possibility and would like to ask for some
suggestions on it. Keeping all data in the same table, but instead of having
one column for Service (A, S, W), have a column that is called Summer and one
Called Winter. Then having either a yes/no box or check box to determine
when system is serviced. IF it is A, than there would be checks in both
boxes. Would it then be possible to have a report look only at that column
and only include it if there is a check mark in the specified column???? If
you wanted only Summer, the report would not look for information in the
winter column?? Is something like this possible????
 
S

Steve Schapel

Tia,

What you are suggesting, i.e. two separate fields for Summer and Winter,
would certainly be possible. But in my opinion it is inferior to the
way you are doing it now, and I would not recommend it. In the long
run, it is guaranteed to make things more difficult and unnecessarily
complicated in a number of ways. I think it would be better to try to
resolve the problem with the reports. Unless I am missing something,
this should be very simple. This is what I don't understand: "the
reports have trouble distinguishing between the Service (A,S,W)". The
reports don't have a mind of their own, they do what you tell them to.
You have complete control over what data goes into the report, and how
the Service is distinguished. As I mentioned previously, the two
standard ways of doing this distinguishing is either via the criteria of
the query that the report is based on, or via the Where Condition
argument of your OpenReport method when you print the report. In either
case, if you want the report to show the Summer Service, and this is to
include the Annual, then it should be *very* simple to get the records
with "'S' Or 'A'" in the Service field. Can you tell me which of these
two approaches you use in trying to get the right data into the report?
If it's the query criteria approach, can you post back with the SQL
view of one of the troublesome queries? If you use code to print the
report, can you post back with the relevant procedure? We will need to
get detailed here if I am to be able to offer any specific advice.
 
S

Steve Schapel

Tia,

You are opening the Report in Preview. If you look at the preview of
the report, does it show the required data correctly? I would expect
that it does. However, are you expecting that the OutputTo method is
then going to pick up the "filter" as you have applied it to the
OpenReport method? If so, sorry, that's not how it works. You tell
Access to OpenReport in preview, and apply a filter to the data, and
that's what it does. You tell Access to OutputTo a report, and that's
what it does. If you tell Access to do both, then that's what it does,
but there's no connection between the two actions.

I would not do it like this. I would have one report, one query, one
button on the form. I would have the report based directly on the
query. I would have unbound controls on the form where I
entered/selected the required employee, week, and service. And I would
enter the criteria in the query via a reference to the form controls,
using syntax such as...
[Forms]![NameOfForm]![Employee]

--
Steve Schapel, Microsoft Access MVP
Thanks for your advice....Hopefully, I can explain this so it makes sense.
I've sought help in the past from newsgroups and haven't been able to find a
solution that will work....So I greatly appreciate your help!!

I have a report that is called ServiceReport it has a subreport called
ServiceSubreport. The ServiceReport contains the Customer Name, Address and
the tests to be run on the specified system. The ServiceSubreport lists the
system. These are linked by Type_of_System. I have a query that sorts the
information according to my needs. For example, Bart S1, is for Bart's
service for Summer week 1. The query has all the correct information. I
then have a form that has 20 buttons. There is a button for each employee
(Bart/Brad) and a button for each week of the summer and each week of the
winter. I've attached the code behind Bart S1 below so you can see what it
says. The button opens Service Report and then applies the Bart S1 (or
corresponding Query) to the ServiceReport as a filter. I've, also, attached
a copy of this code below. To some degree this works because it does pull
all the systems according to the defined week but it does not distinguish
between the Service (A,S,or W). However, if you open the query, only the
systems with the defined week and service are displayed. When I've tried
sorting according
to the Service, I then end up with Reports that have all systems A on one
page, and S on another, and then W on another. I need all systems for the
specified week of A and S to appear on the same report, and to not display
any systems from the remaining Service. It is complicated because I need
each Type_of_System to be seperated out so that each customer has a seperate
report for each Type_of_System that we service from their account. I can get
my report to do this by using the sort command on the report.

As of right now, I can get the right sorting and grouping on the report,
except, that the service will not distinguish according to what I think I've
designed.

Here's a copy of all the code:

This is the code behind the button for Summer week 1 for Bart on the
ServiceReports Form:

'------------------------------------------------------------
' Bart_Week_S1_Click
'
'------------------------------------------------------------
Private Sub Bart_Week_S1_Click()
On Error GoTo Bart_Week_S1_Click_Err

DoCmd.OpenReport "ServiceReport", acViewPreview, "Bart S1", "", acNormal
DoCmd.OutputTo acReport, "ServiceReport", "RichTextFormat(*.rtf)",
"Service Report.rtf", False, "", 0


Bart_Week_S1_Click_Exit:
Exit Sub

Bart_Week_S1_Click_Err:
MsgBox Error$
Resume Bart_Week_S1_Click_Exit

End Sub '------------------------------------------------------------

There is a button on the form for 2 employees for both Summer and Winter
weeks of service.


Bart S1 is a query with the following code:

SELECT *
FROM [System Information]
WHERE ((([System Information].Employee)="Bart") AND (([System
Information].Service)="A") AND (([System Information].Week)=0)) OR ((([System
Information].Employee)="Bart") AND (([System Information].Service)="A") AND
(([System Information].Week)=1)) OR ((([System Information].Employee)="Bart")
AND (([System Information].Service)="A") AND (([System Information].Week)=7))
OR ((([System Information].Employee)="Bart") AND (([System
Information].Service)="S") AND (([System Information].Week)=0)) OR ((([System
Information].Employee)="Bart") AND (([System Information].Service)="S") AND
(([System Information].Week)=1)) OR ((([System Information].Employee)="Bart")
AND (([System Information].Service)="S") AND (([System Information].Week)=7));

There is a query for each week of the Summer and each week of the winter.

Hopefully, I've explained my problem.....If you need more information,
please let me know. Thank you again for all of your help!!!
Click to expand...
 
Top