Solved

Adding Null amounts into a Continous form query.

Posted on 2006-11-01
8
249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 65

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 125 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

742 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