Dlookup Issue

[Clarkie001]

Hi,

I have a field ina query in which I lookup up another table to return a number based on a project code.

The issue I am getting is it will only return the same number for each row, when there are clearly more than one project number.....I can't see what the problem is.

any help in this matter will be greatly appreciated.

Here is the sql of the query.

SELECT FMS_Data.Date, FMS_Data.FMS_ID, [FMS_Data]![Campaign_Code] & [FMS_Data]![Source_Code] AS Campaign_Source, Int(DLookUp("[APPLIC_ID]","FMS_CODES","[Campaign_Source]=[FMS_CODES].[Campaign_Source]&quot) AS APPLIC_ID INTO FMS_Sales_mtbl
FROM AGENT_DETAILS INNER JOIN (FMS_Data INNER JOIN CURR_REPOR ON FMS_Data.Date = CURR_REPOR.REPOR_DATE) ON AGENT_DETAILS.FMS_ID = FMS_Data.FMS_ID
GROUP BY FMS_Data.Date, FMS_Data.FMS_ID, [FMS_Data]![Campaign_Code] & [FMS_Data]![Source_Code], Int(DLookUp("[APPLIC_ID]","FMS_CODES","[Campaign_Source]=[FMS_CODES].[Campaign_Source]&quot)
HAVING ((([FMS_Data]![Campaign_Code] & [FMS_Data]![Source_Code]) In (select Campaign_Source from FMS_CODES where Disabled=false)));

Thanks

Clarkie

 

[SteveR77]

Clarkie,

The Group By may be what is getting you. If all the records contain similiar data for the fields selected the Group By will do a summation that may result in there only being one value.

Try running the query without the summing option on and see what the data is.

HTH,

Steve

 

[Hap007]

Dear Clarkie,

Why would you use the 'DLookup' function when you could just include the second table in your SQL and retrieve your needed info directly?

Hap..

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[Clarkie001]

Hi Hap007,

There is no link possible between these two tables.

Clarkie

 

[Clarkie001]

Hi Steve,

I have removed the aggregate function and it is still providing the same results.....any ideas? argh!!

 

[Hap007]

Dear Clarkie,

Well then, break the thing into 2 parts.

Try building your recordset into a query, (First query) and then create a second query that actuals does the summing and grouping using the first query as input. Make sure that the 'DLookup' is only in the First query.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers