?
Solved

Adding Null amounts into a Continous form query.

Posted on 2006-11-01
8
Medium Priority
?
263 Views
Last Modified: 2008-03-17
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?
0
Comment
Question by:macphie
  • 4
  • 3
8 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17850045
Instead of YourValue as the control source of the textbox, use =Nz([YourValue ],0)
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17850101
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]=[b].[PeriodDate],[Value],0)) AS Expr1
FROM DateTable AS a, ActualFigures AS b
GROUP BY a.PeriodDate, b.productCode, b.CustomerCode;
And this becomes the form's record source
0
 
LVL 1

Author Comment

by:macphie
ID: 17850257
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.


0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 26

Expert Comment

by:jerryb30
ID: 17850312
Please post your SQL linking the two tables.
0
 
LVL 1

Author Comment

by:macphie
ID: 17850440
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.
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 375 total points
ID: 17850552
My concept is that tbl_Months only has one field, all possible Period values.
That being the case, what you want is:

SELECT tbl_months.Period, qry_g2005_values.productCode, qry_g2005_values.CustomerNumber, qry_g2005_values.[Delivery Point], qry_g2005_values.quantity, Sum(IIf([tbl_months].[Period]=[qry_g2005_values].[Period],[qry_g2005_values].[Value],0)) AS VVALUE
FROM tabl_month,qry_g2005_values
GROUP BY atbl_Months.Period, qry_g2005_values.productCode, qry_g2005_values.CustomerNumber, qry_g2005_values.[Delivery Point], qry_g2005_values.quantity;

Not a union.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17851312
Did the sql work for you?
0
 
LVL 1

Author Comment

by:macphie
ID: 17856406
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question