Is there a way of displaying specific fields, either in a table or form, only
when a certain value is inputted in a previous field? For example, the field
'Marriage Date' is shown only when 'Engaged' is selected from the previous
field containing a drop down menu.
As this is the 'tablesdbdesign' group...
Let's say you have three possible states: couples who are not engaged,
engaged couples who have 'named the day' and engaged couples who have
yet to 'name the day'. This is one of those rare occasions where I
might use a nullable column e.g. (ANSI-92 Query Mode syntax):
CREATE TABLE Couples
(
name1 VARCHAR(35) NOT NULL UNIQUE,
name2 VARCHAR(35) NOT NULL UNIQUE,
CHECK (name1 <> name2),
UNIQUE (name1, name2)
)
;
CREATE TABLE EngagedCouples
(
name1 VARCHAR(35) NOT NULL,
name2 VARCHAR(35) NOT NULL,
FOREIGN KEY (name1, name2)
REFERENCES Couples (name1, name2),
marriage_date DATETIME
)
;
INSERT INTO Couples (name1, name2) VALUES ('Charlie', 'Sam')
;
INSERT INTO Couples (name1, name2) VALUES ('Leslie', 'Hillary')
;
INSERT INTO EngagedCouples (name1, name2, marriage_date) VALUES
('Leslie', 'Hillary', NULL)
;
INSERT INTO Couples (name1, name2) VALUES ('Jean', 'Lindsay')
;
INSERT INTO EngagedCouples (name1, name2, marriage_date) VALUES
('Jean', 'Lindsay', #2008-01-05 00:00:00#)
;
The usual way to query such a structure is to get a single flat
resultset of rows and columns e.g.
SELECT C1.name1, C1.name2, E1.marriage_date
FROM Couples AS C1
LEFT JOIN EngagedCouples AS E1
ON C1.name1 = E1.name1
AND C1.name2 = E1.name2;
The problem is, there's no way of differentiating between couples who
are not engaged and engaged couples who have yet to 'name the day'
because the NULL value is being used to indicate 'date not yet set'
and 'date does not apply'.
The ability to 'hide' columns in the resultset would require a
completely new SQL syntax. Happily, such syntax is available via
Microsoft's data shaping technology e.g.
SHAPE
{
SELECT Name1, Name2
FROM Couples;
} APPEND
(
{
SELECT Name1, Name2, marriage_date
FROM EngagedCouples;
} AS chapMarriageDates
RELATE Name1 TO Name1, Name2 TO Name2
)
Sadly, the Access user interface in general lags behind technological
advancements and this is no exception. While I could past a VBA+ADO
+Jet example, you'd need a hierarchical control to view the results
and there's no SQL DDL for that <g>.
Jamie.
--
