Report - Pull information based on group field (1 Viewer)

akb · Jul 21, 2014

My report is grouped by a Billing Group #...within each billing group there are sub invoice numbers that are tied to the main billing group # (they all have the same billing group # but different general invoice numbers). I need to pull data if one of the general invoice # invoice dates is greater than or equal to a specific date entered.

In other words: If a Billing Group # and one of the Invoice Dates is greater than or equal to a specific date entered then pull ALL GROUP data to report.

Does anyone have the formula I would use for this?

I appreciate your help!

GinaWhipp · Jul 21, 2014

You could use a Form to filter by and then have a buton to show your Report, adapting something like this...
http://allenbrowne.com/ser-62.html

vbaInet · Jul 21, 2014

It looks like you may have a form already. Where are you entering this date?

akb · Jul 22, 2014

Typically I create a parameter query, create the report based on the query information and then create a macro to run the parameter query/report. I have created a number forms as well though.

I don't know how to tell the report to pull all fields in a group if at least one of the invoice dates is greater than or equal to the date I enter as the parameter. Note some of the dates could be less than the parameter date but I need those to pull through if at least one is greater.

I hope I am making sense..

vbaInet · Jul 22, 2014

akb said:
I don't know how to tell the report to pull all fields in a group if at least one of the invoice dates is greater than or equal to the date I enter as the parameter.

Greater than or equal to the parameter name is what you put in the Criteria for the date field. You can repeat the same parameters as long it's the exact text.

akb · Jul 22, 2014

Do I need to have my query sorted by the billing group invoice number then?

Is this the best way to do this? Or do you recommend a form?

vbaInet · Jul 22, 2014

You don't necessarily need to apply any sort. If you need to sort, do it in the report itself.

I would recommend a form.

akb · Jul 22, 2014

Maybe I'm not making sense...Because if I enter the criteria like you said above, it just pulls any dates greater than or equal to what I entered. I'll try again.

My report is grouped by a Billing Invoice #...within each group there are sub invoice #'s that are tied to the main billing invoice # (they all have the same billing invoice # but different sub invoice #'s). I need to pull data if one of the invoice dates for any of the sub invoice #'s is greater than or equal to a specific date entered.

Example:
Billing Invoice #: 205658 Contract Amount: $50,000.00 Total Billed: $50,000.00
Sub Invoice #1: 205659 Invoice Date: 20140109 Invoice Total: $20,000.00
Sub Invoice #2: 205670 Invoice Date: 20140410 Invoice Total: $20,000.00
Sub Invoice #3: 205720 Invoice Date: 20140620 Invoice Total: $10,000.00

I need to run the report on all orders that have been billed since 6/1/2014, since the data above has at least one sub invoice # with a date greater than 6/1/2014 I need the report to pull all fields for this specific Billing Invoice #.

GinaWhipp · Jul 23, 2014

Have you looked at the link I provided? It has a sample to download and should get you started with your Form.

vbaInet · Jul 23, 2014

akb said:
Example:
Billing Invoice #: 205658 Contract Amount: $50,000.00 Total Billed: $50,000.00
Sub Invoice #1: 205659 Invoice Date: 20140109 Invoice Total: $20,000.00
Sub Invoice #2: 205670 Invoice Date: 20140410 Invoice Total: $20,000.00
Sub Invoice #3: 205720 Invoice Date: 20140620 Invoice Total: $10,000.00

I need to run the report on all orders that have been billed since 6/1/2014, since the data above has at least one sub invoice # with a date greater than 6/1/2014 I need the report to pull all fields for this specific Billing Invoice #.

So all the other sub invoices that don't meet the criteria don't pull through right?

What will happen to the Billing Invoice if none of the sub invoices meet the criteria? Will it still show that Billing Invoice or will it be suppressed it?

We also need to see the Record Source of your report.

akb · Jul 23, 2014

GinaWhipp - Yes I did receive, I'm not sure what I would use for my date range to search though so the form reads it?

vbaInet -
There could be multiple Billing Invoice #'s pulling through with all of their Sub Invoice #'s.

As long as ONE of the invoice dates of a Sub Invoice #'s meets the criteria then it should pull through ALL Sub Invoices whether they have an invoice date or not.

If none of the sub invoices meet the criteria, then the Billing Invoice will not come through - this means nothing was billed for the order during the criteria entered.

The fields used for the report are from multiple tables from our software - the tables are joined in the query I built. The query is the record source of the report.

Thanks all! I really appreciate it

vbaInet · Jul 23, 2014

akb said:
The fields used for the report are from multiple tables from our software - the tables are joined in the query I built. The query is the record source of the report.

I still need to see how the SQL is built in order to know where you should apply the criteria. And if you could send some sample data (just some jibberish but realistic) that would be helpful.

akb · Jul 23, 2014

Sorry...so a sample of my SQL?

vbaInet · Jul 23, 2014

The SQL of your query and sample fictitious records in an Excel sheet.

akb · Jul 23, 2014

See attached sample data and here is my SQL:

SELECT dbo_Header.Inv_Num, Right([dbo_GroupHead.Description],8) AS BG_Num, Format([Grand_Inv_Total],"Currency") AS Inv_Total, dbo_GroupHead.ContractAmount, IIf(IsNull([invprtdate]),"$0.00",[Inv_Total]) AS Billed, dbo_Header.InvPrtDate, Nz([InvPrtDate],0) AS Bill, Format(CDate(Format([DateEntered],"0000-00-00")),"mm/dd/yyyy") AS Start, dbo_Header.Cust_Name, dbo_Header.Cust_Addr1, dbo_Header.Cust_City, dbo_Header.Cust_State, dbo_Header.Cust_Zip, dbo_Header.Job_Name, dbo_Header.JobAddr1, dbo_Header.Job_City, dbo_Header.Job_State, dbo_Header.Job_Zip, dbo_Header.Salesman1, dbo_Header.Salesman2, dbo_Header.Email, AdSource.source, ServiceType.description, ContractType.description, dbo_Header.Closed
FROM ((((dbo_Header LEFT JOIN AdSource ON dbo_Header.AdSource = AdSource.type) LEFT JOIN ServiceType ON dbo_Header.ServiceType = ServiceType.type) LEFT JOIN ContractType ON dbo_Header.ContractType = ContractType.type) LEFT JOIN dbo_GroupDetail ON dbo_Header.SeqNum = dbo_GroupDetail.HeaderSeqNum) LEFT JOIN dbo_GroupHead ON dbo_GroupDetail.GroupHeadSeqNum = dbo_GroupHead.SeqNum
WHERE (((Right([dbo_GroupHead.Description],8)) Is Not Null) AND ((dbo_GroupHead.ContractAmount)>=9999.99) AND ((dbo_Header.Voided)=0) AND ((dbo_Header.Job_Type) In (17,18)))
ORDER BY Right([dbo_GroupHead.Description],8);

vbaInet · Jul 23, 2014

Before you do the join between Header (which is your Invoice table) and GroupHead (your Billing Group table), apply the criteria in the Invoice table alone in a separate query, save it and use this new query to join to GroupHead and so forth.

akb · Aug 18, 2014

I took a break from this because I was frustrated with it - now revisiting the report.

I'm still confused - what criteria am I going to apply in the Invoice table?

vbaInet · Aug 18, 2014

You've left this for too long and now I don't remember what your thread relates too.

Like I said before, break up the query into smaller ones first and test the criteria in the relevant query. What is the name of your invoice table?

akb · Aug 18, 2014

Invoice table is named Header

vbaInet · Aug 18, 2014

So create a query based on just Header and test it with these two criteria:

Code:

((dbo_Header.Voided)=0) AND ((dbo_Header.Job_Type) In (17,18)))

Report - Pull information based on group field (1 Viewer)

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread