SQL blues...

[Art Heyman]

Primary table and 7 associated tables (used for schedules; table are of
hours in the days of the week).

Main table had ID ; day tables have CID as foreign key.

I constructed a form based on a query linking the main table to the 7 day
tables. This worked with one day used, and failed with more than one. I
experimented with all 6 possible join properties , varying the left and
right table names and all three include choices.

Access was vauge on the reason for the failure, but did talk of primary key
issues. I don't see how I violated any ...

I have since given up and resorted to a large flat table .. is there a way
to have made this work ?

 

[Duane Hookom]

Sounds like an issue caused by an un-normalized table structure. Seven
repeating tables isn't generally a good idea.

We have very little knowledge of:
-your current table structure
-your data
-what you are attempting to accomplish

 

[HSalim]

Art,
This is so funny!
One would think you are talking about your girlfriend
I doubt Access was vague, and that it talked or had issues.

Please post the structure of your flat table - Here is an example of what
would help us help you
ColumnName Type Description
ID AutoNumber Primary key
Date1 date stores first date of ...
....
yes, each and every column with datatype and a brief explanation of how it
is used and we'll show you how to split it up.

HS

 

[Art Heyman]

Duane :

The form is based on this query:

SELECT main.*, mon.*, tue.*, wed.*, sat.*, sun.*, thu.*, fri.*
FROM ((((((main INNER JOIN mon ON main.ID = mon.cid) INNER JOIN sat ON
main.ID = sat.cid) INNER JOIN sun ON main.ID = sun.cid) INNER JOIN thu ON
main.ID = thu.cid) INNER JOIN fri ON main.ID = fri.cid) INNER JOIN tue ON
main.ID = tue.cid) INNER JOIN wed ON main.ID = wed.cid;


The day tables look like:
ID Autonumber
CID Number
7-8 Yes/No
9-10 Yes/No
Etc.

The main table :

ID Autonumber
Last text
First text
Etc.


Access responds with :

" The changes you requested to the table were not successful as they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field that contains duplicate data, remove the index
or redefine the index to permit duplicate entries..."

The idea is to create a structure allowing for storage and reporting of
clients with similar hours for lessons across the week. The flat system is
now working, but I'm very curious abut this problem and would have loved to
have based a form on the query. Any help would be greatly appreciated.


Art Heyman

 

[Duane Hookom]

I'm not sure why you are using this type of table structure. It looks a bit
like a scheduling application. I would not have separate tables for each day
and not have separate fields for each time slot. Your structure will
continue to cause issues unless you can change it.

I would have a single table with 4 fields that would replace seven tables
and possibly a hundred fields.
tblSchedule
==================
SchedID
SchedDate
TimeSlot
CID

 

[Art Heyman]

Duane :

The form is based on this query:

SELECT main.*, mon.*, tue.*, wed.*, sat.*, sun.*, thu.*, fri.*
FROM ((((((main INNER JOIN mon ON main.ID = mon.cid) INNER JOIN sat ON
main.ID = sat.cid) INNER JOIN sun ON main.ID = sun.cid) INNER JOIN thu ON
main.ID = thu.cid) INNER JOIN fri ON main.ID = fri.cid) INNER JOIN tue ON
main.ID = tue.cid) INNER JOIN wed ON main.ID = wed.cid;


The day tables look like:
ID Autonumber
CID Number
7-8 Yes/No
9-10 Yes/No
Etc.

The main table :

ID Autonumber
Last text
First text
Etc.


Access responds with :

" The changes you requested to the table were not successful as they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field that contains duplicate data, remove the index
or redefine the index to permit duplicate entries..."

The idea is to create a structure allowing for storage and reporting of
clients with similar hours for lessons across the week. The flat system is
now working, but I'm very curious abut this problem and would have loved to
have based a form on the query. Any help would be greatly appreciated.


Art Heyman

 

[Art Heyman]

The form is based on this query:

SELECT main.*, mon.*, tue.*, wed.*, sat.*, sun.*, thu.*, fri.*
FROM ((((((main INNER JOIN mon ON main.ID = mon.cid) INNER JOIN sat ON
main.ID = sat.cid) INNER JOIN sun ON main.ID = sun.cid) INNER JOIN thu ON
main.ID = thu.cid) INNER JOIN fri ON main.ID = fri.cid) INNER JOIN tue ON
main.ID = tue.cid) INNER JOIN wed ON main.ID = wed.cid;


The day tables look like:
ID Autonumber
CID Number
7-8 Yes/No
9-10 Yes/No
Etc.

The main table :

ID Autonumber
Last text
First text
Etc.

Access responds with :

" The changes you requested to the table were not successful as they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field that contains duplicate data, remove the
index or redefine the index to permit duplicate entries..."

The idea is to create a structure allowing for storage and reporting of
clients with similar hours for lessons across the week. The flat system is
now working, but I'm very curious abut this problem and would have loved
to have based a form on the query. Any help would be greatly appreciated.