Recent content by Darren Bartrup

It seems like you want the null values to show as 0's. Will the addition of the NZ function to my original query help? SELECT NZ(Table1.Dept,0) As Dept, NZ(Actual_People,0) As Act_People, NZ(Actual_Income,0) As Act_Income, NZ(Budget_People,0) As Bud_People, NZ(Budget_Income,0) As Bud_Income...

Thought I'd use the Actual_People, Actual_Income for the answer as I still have the query on my computer. So, to recap: You have table 1 which holds Dept, Actual_People, Actual_Income: Dept......................Actual_People...........Actual_Income...

I would use a UNION query: SELECT Table1.Dept, Actual_People,Actual_Income, Budget_People, Budget_Income FROM Table1 LEFT JOIN Table2 ON Table1.Dept = Table2.Dept UNION SELECT Table2.Dept, Actual_People,Actual_Income, Budget_People, Budget_Income FROM Table2 LEFT JOIN Table1 ON Table2.Dept =...

Sorry, haven't had a look at your database but an inital idea is to use UPDATE and DELETE queries based on your select query? If I can (read as if my other half lets me) I'll log on at home tonight and take a look.

Hi Jomuir, You'll need two queries to accomplish this. The first query will update your Ref_No field: UPDATE tblCustMemo SET tblCustMemo.Ref_No = Left([Memo],13); And the second will remove the characters from your memo field: UPDATE tblCustMemo SET tblCustMemo.[Memo] =...

Glad it helped point you in the right direction, even if it wasn't the complete answer you were after. Darren

Hardly wizardly powers, but thanks for the compliment anyway. Just finished a post-graduate course on database design and want to keep my brain & SQL understanding up to speed :)

Create a normal select query - and put all your fields into the query. Now change to SQL view and your query should look like this: SELECT Jan1.fldDate, Jan1.field1, Jan1.field2 FROM Jan1; Now update the SQL to look like this: SELECT fldDate, field1, field2 FROM Jan1 UNION ALL SELECT fldDate...

Ok, think I've got it now: SELECT tblProjectID.ProjectID, tblProjectID.StepID, NZ(SUM(tblPayments.Total),0)+NZ(SUM(tblReceivables.Total),0) As GrandTotal FROM (tblProjectID LEFT OUTER JOIN tblPayments ON ((tblProjectID.ProjectID = tblPayments.ProjectID) AND (tblProjectID.StepID =...

Actually, got that wrong - missed the step bit, and you want a grand total: SELECT tblProjectID.ProjectID, tblProjectID.StepID, SUM((SELECT SUM(Total) FROM tblPayments WHERE ((tblPayments.ProjectID = tblProjectID.ProjectID) AND (tblPayments.StepID = tblProjectID.StepID))))+ SUM((SELECT...

Would something like this point you in the right direction: SELECT tblProjectID.ProjectID, (SELECT SUM(TOTAL) FROM tblPayments WHERE tblPayments.ProjectID = tblProjectID.ProjectID) AS Total1, (SELECT SUM(TOTAL) FROM tblReceivables WHERE tblReceivables.ProjectID = tblProjectID.ProjectID) AS...

Hi spoole, Enter something along these lines into the SQL view of your query: SELECT inv1 FROM Table1, Table2 WHERE inv1>inv2 ORDER BY inv1; inv1 is in Table1 and inv2 in Table2. Edit: To busy getting a user name and missed that it had already been answered. I wouldn't think you'd need a...