Only the first result gets a value

[thesonofdad]

Hi all,

Fun problem for everyone over the weekend - :)

OK so I have the following SQL query in Access.....

SELECT x.SURVEY_DATE AS [Survey Date], z.ORIG_CONST_DATE, z.ORIG_EXP_NO
FROM [select * from x00]. AS x, [select * from zG]. AS z;

Note: x00 and zG are other queries in the same MDB File
Note also: zG only returns one record

The problem is that only the first record returned gets a value for z.ORIG_CONST_DATE but all records get a value for z.ORIG_EXP_NO. Why is this? If z is a table with one record all records get a date....Why does it matter if zG is a table or query?? Also if this is the normal behavior how do i put the z.ORIG_CONST_DATE into all records??

Thankyou everyone in advance for your wonderful work!!!

For completeness:

x00:
//returns about 20 records
SELECT SURVEY_DATE FROM MON_DIS_PADIAS_JPCC WHERE STATE_CODE=STATE And SHRP_ID=SHRP;

zG:
//returns 1 record
SELECT GPS_SPS+EXPERIMENT_NO AS ORIG_EXP_NO, ORIG_CONST_DATE FROM EXPERIMENT_SECTION
WHERE CONSTRUCTION_NO=1 AND STATE_CODE = [STATE] AND SHRP_ID = [SHRP];

[jimatqsi]

I think you're going to need to add a JOIN statement... add a keyfield to each query and join them together with it. Here's an example from something I'm doing...

SELECT Family.*, Family.FamilyName, Items.Item AS Expr1
FROM Family LEFT JOIN Items ON Family.FamilyCode = Items.FamilyCode;
and

SELECT Family.*, Family.FamilyName, Items.Item AS Expr1
FROM Family RIGHT JOIN Items ON Family.FamilyCode = Items.FamilyCode;

The first one gives me all 1,087 items in my Item table, and the Family name of each item.

The second one gives me 1,093 records.. I'm getting all 15 of the families listed and any of the Items coded for those families. Six of them have a blank where the Item number should be. That's because there are 6 familes defined that have no item numbers coded to be in that family (the JOIN failed in those cases)

Let me know if this helps or not.
Jim