macphie
asked on
Adding Null amounts into a Continous form query.
Hello, I require to show a Continous form, that will show a Period Date Product Code Customer Code and a Value.
This will originally come form a Table of Actual Figures that hold these fields.
Thing is I need to show a Full Years Data, while not all products will have been bought each Month.
Ok so the actual table looks like 200604 TS123 1020344 45.00
200605 TS123 1020344 75.00
200607 TS123 1020344 85.00
I need the form to show this but to Fill in the missing value info with Zero's so Like this:
200604 TS123 1020344 45.00
200605 TS123 1020344 75.00
200607 TS123 1020344 0.00
200607 TS123 1020344 85.00
What is the best method to try?
This will originally come form a Table of Actual Figures that hold these fields.
Thing is I need to show a Full Years Data, while not all products will have been bought each Month.
Ok so the actual table looks like 200604 TS123 1020344 45.00
200605 TS123 1020344 75.00
200607 TS123 1020344 85.00
I need the form to show this but to Fill in the missing value info with Zero's so Like this:
200604 TS123 1020344 45.00
200605 TS123 1020344 75.00
200607 TS123 1020344 0.00
200607 TS123 1020344 85.00
What is the best method to try?
Jim Horn
Instead of YourValue as the control source of the textbox, use =Nz([YourValue ],0)
You are actually trying to infer a [Period Date] field that does not exist in the table?
You would perhaps have to create a table with all possible period dates, and tie it to your ActualFigures table.
SELECT a.PeriodDate, b.productCode, b.CustomerCode, Sum(IIf([a].[PeriodDate]=[
FROM DateTable AS a, ActualFigures AS b
GROUP BY a.PeriodDate, b.productCode, b.CustomerCode;
And this becomes the form's record source
You would perhaps have to create a table with all possible period dates, and tie it to your ActualFigures table.
SELECT a.PeriodDate, b.productCode, b.CustomerCode, Sum(IIf([a].[PeriodDate]=[
FROM DateTable AS a, ActualFigures AS b
GROUP BY a.PeriodDate, b.productCode, b.CustomerCode;
And this becomes the form's record source
ASKER
Yes I'm looking to infer a Period Date field that doesn't exist in the table...
I have created the other table and linked these, but as it's a continuous form the i Get 2 dates and not Grouped.
So I get
200604 TS123 1020344 0.00
200604 TS123 1020344 45.00
200605 TS123 1020344 0.00
200605 TS123 1020344 75.00
200606 TS123 1020344 0.00
200607 TS123 1020344 0.00
200607 TS123 1020344 85.00
and I cannot seem to get the Union table to group this.
I have created the other table and linked these, but as it's a continuous form the i Get 2 dates and not Grouped.
So I get
200604 TS123 1020344 0.00
200604 TS123 1020344 45.00
200605 TS123 1020344 0.00
200605 TS123 1020344 75.00
200606 TS123 1020344 0.00
200607 TS123 1020344 0.00
200607 TS123 1020344 85.00
and I cannot seem to get the Union table to group this.
Please post your SQL linking the two tables.
ASKER
SELECT Period, [Customer Number], [Delivery Point], [Product Code], Quantity, Value
FROM Qry_g2005_Values
UNION SELECT Period, [Customer Number], [Delivery Point], [Product Code], Quantity, Value
FROM tble_Months
GROUP BY Period, [Customer Number], [Delivery Point], [Product Code], Quantity, Value;
As requested.
FROM Qry_g2005_Values
UNION SELECT Period, [Customer Number], [Delivery Point], [Product Code], Quantity, Value
FROM tble_Months
GROUP BY Period, [Customer Number], [Delivery Point], [Product Code], Quantity, Value;
As requested.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did the sql work for you?
ASKER
Thanks Jerryb, that did the job, should've known that as well I fell in the trap of "it's more difficult than I think".
Thanks again for that.
Mike
Thanks again for that.
Mike