Force a Query to Insert "Placeholders" (?)

[croy]

Access 2002

tbl_Main Contains a date field. There are 100s of records
in this table

tbl_Sub contains a one-to-many link field for tbl_Main, and
a field for colors. There are 3 records in this table.

I wish to build a query that will show a row for each
tbl_Main record, and also a row for each of the colors that
are not linked to that record, showing the nulls, and puting
the colors in a certain sort order, whether a tbl_Main
record has a related record in tbl_Sub or not. Sort of a
place-holding record-set.

Like this:

tbl_MainId.cDate tbl_Sub.Color
1/1/2001 Blue
red (not really there, but
I want the third color
to show)
9/17/2004 green
Blue (not really there, but
I want the third color
to show)
Red (not really there, but
I want the third color
to show)
12/1/2000 Green

.... and so on.

For the life of me, I can't seem to get this to work.

Any ideas appreciated.

 

[Guest]

Is this what you want --
SELECT tbl_MainId.cDate, tbl_Sub.Color
FROM tbl_MainId, tbl_Sub
ORDER BY tbl_MainId.cDate, tbl_Sub.Color;

 

[croy]

Thanks for the reply, Karl.

Is this what you want --
SELECT tbl_MainId.cDate, tbl_Sub.Color
FROM tbl_MainId, tbl_Sub
ORDER BY tbl_MainId.cDate, tbl_Sub.Color;

Not really. Here's what I get from that:

cDate Color
1/1/1991 Blue
1/1/1991 Green
1/1/1991 Red
5/1/1991 Blue
5/1/1991 Green
5/1/1991 Red
7/1/1993 Blue
7/1/1993 Green
7/1/1993 Red
1/1/1995 Blue
1/1/1995 Green
1/1/1995 Red
5/1/1995 Blue
5/1/1995 Green
5/1/1995 Red

Here's what I'd like to get:

cDate Color
1/1/1991 Blue
Green
Red
Blue
Green
5/1/1991 Red
Blue
7/1/1993 Green
Red
1/1/1995 Blue
Green
1/1/1995 Red
Blue
Green
5/1/1995 Red

Where the dates show, there's actually a related record.

 

[Guest]

What about this ---
SELECT tbl_MainId.cDate,
IIf([tbl_Sub].[cDate]=[tbl_MainId].[cDate],[Color],"") AS [" "],
IIf([tbl_Sub].[cDate]=[tbl_MainId].[cDate]," ",[Color]) AS Colors
FROM tbl_MainId, tbl_Sub;

 

[croy]

What about this ---
SELECT tbl_MainId.cDate,
IIf([tbl_Sub].[cDate]=[tbl_MainId].[cDate],[Color],"") AS [" "],
IIf([tbl_Sub].[cDate]=[tbl_MainId].[cDate]," ",[Color]) AS Colors
FROM tbl_MainId, tbl_Sub;

Nope. Still shows a date where there should be none.