form based on query ?

[Art Heyman]

Trying to create a form based on a fairly complex query; this is for a
schedule system , storing hours clients are available for
lessons. I have one main table of basic client info, and seven smaller
tables for days of the week.

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.

 

[Michel Walsh]

Hi,


From the query or the context you supply, the "change" you want to do,
the operation you try to make it, to be more exact, is not explicitly given.

If you try to make one table out of the seven small ones (to get back
the "day" as value, not as name of a table), you could try using a UNION
query


SELECT 2 As theDay, cid FROM mon
UNION ALL
SELECT 3, cid FROM tue
UNION ALL
....
UNION ALL
SELECT 6, cid FROM fri


and thus, your query just become


SELECT main.*, myUnionQuery.theDay
FROM main INNER JOIN myUnionQuery
ON main.cid=myUnionQuery.cid



Sure, if you need the query to be updateable, you have to turn the union
query into a table (since a union query IS NOT updateable).



Hoping it may help,
Vanderghast, Access MVP