madanthrax
IS-IT--Management
Hi guys,
I have been given access to a 'view' from another SQL database which is on the same physical server as my website's database. I have no control over this other database.
All the raw details about a few thousand documents in pdf format are available. The other database classifies the docs into six different groups. My website has to classify them into 17 different groups..AND documents can be in more than one group.
From advice I got on this forum I made a table in my DB that was 'one to many' as they say. All the 'view' docs have a PubID, so I have a table called SubAreas with a field for PubID and SubID, I added another field called Control (more later). The PubID is linked to the PubID in the view by me making a further view in my DB using a left outer join.
This works well, if multiple rows are inserted in the SubAreas table such as:
PubID SubID
2267 120
2267 150
2267 220
(SubID being the code for one of the 17 groups) Then using a recordset with a variable to specify the group number any doc associated with the number displays on my page. This is really cool, and my first attempt at using linked tables.
So what is the problem?
Well I have made a form up with 17 checkboxes on it for my users to classify each of their document's areas. Using a select box they choose the document PubID and tick any number of boxes. Submitting the form which has a move next in it inserts a row for every ticked box just like my example above. The dropdown list is populated by a recordset, and displays about 2000 PubIDs.
I have cut down the number showing by filtering them by the six original view's groups and putting them in different forms which helps a bit, but I need a way of hiding submitted PubIDs or users will forget which docs they have classified.
So I created the Control field in the SubAres table. When the checkbox form is submitted it puts the value 1 into each Control field row. I thought I could select the dropdown recordset results by using the SQL: Where Control = "" , or Where Control <> 1, or Where Control is null, but it does not work.
There is no PubID record at all until an insert is made. Having a default value in the Control field also will not help until it exists.
Is there some kind of EOF value that can be put in an SQL statement? or record does not exist? Or do I need to redesign?
Any help would be apprciated.
I have been given access to a 'view' from another SQL database which is on the same physical server as my website's database. I have no control over this other database.
All the raw details about a few thousand documents in pdf format are available. The other database classifies the docs into six different groups. My website has to classify them into 17 different groups..AND documents can be in more than one group.
From advice I got on this forum I made a table in my DB that was 'one to many' as they say. All the 'view' docs have a PubID, so I have a table called SubAreas with a field for PubID and SubID, I added another field called Control (more later). The PubID is linked to the PubID in the view by me making a further view in my DB using a left outer join.
This works well, if multiple rows are inserted in the SubAreas table such as:
PubID SubID
2267 120
2267 150
2267 220
(SubID being the code for one of the 17 groups) Then using a recordset with a variable to specify the group number any doc associated with the number displays on my page. This is really cool, and my first attempt at using linked tables.
So what is the problem?
Well I have made a form up with 17 checkboxes on it for my users to classify each of their document's areas. Using a select box they choose the document PubID and tick any number of boxes. Submitting the form which has a move next in it inserts a row for every ticked box just like my example above. The dropdown list is populated by a recordset, and displays about 2000 PubIDs.
I have cut down the number showing by filtering them by the six original view's groups and putting them in different forms which helps a bit, but I need a way of hiding submitted PubIDs or users will forget which docs they have classified.
So I created the Control field in the SubAres table. When the checkbox form is submitted it puts the value 1 into each Control field row. I thought I could select the dropdown recordset results by using the SQL: Where Control = "" , or Where Control <> 1, or Where Control is null, but it does not work.
There is no PubID record at all until an insert is made. Having a default value in the Control field also will not help until it exists.
Is there some kind of EOF value that can be put in an SQL statement? or record does not exist? Or do I need to redesign?
Any help would be apprciated.
"Nothing is impossible until proven otherwise"
