Help! Adding different queries to Report Control source.

[arus76]

I am trying to create a report using more than one query as control source. There is not relantionship between the queries. What can I do to add the field from 2 Queries with no relantionship to my report?
I appreciate any help
Thanks

 

[scriverb]

You can use the Function DLookup in the Control Source of a control in your Report. This will allow you to lookup unrelated data from other tables and displaying it in the report.

Bob Scriver

 

[arus76]

I tried to use the dlookup function however I can get it to work.

The query that I want to get data from has 1 field:
PMONTH. Month[PERIOD](get the month of the Period), Year[PERIOD](get the year of Period) are the parameter for my query.

dlookup([PMONTH], "QryPriorMonthcostofsale", ...? I need help here setting up my Criteria so I can get the 2 parameters.

 

[scriverb]

Okay, please explain the WHERE criteria for selecting the record that you want from the query. Identify the data types and what you want to select on. I will then finish the DLookup for you.

Bob Scriver

 

[arus76]

I tried to use the dlookup function however I can get it to work.

The query that I want to get data from has 1 field:
PMONTH. Month[PERIOD](get the month of the Period), Year[PERIOD](get the year of Period) are the parameter for my query.

dlookup([PMONTH], "QryPriorMonthcostofsale", ...? I need help here setting up my Criteria so I can get the 2 parameters.

 

[scriverb]

Here is a sample SQL for using two parameters to select a recod from a query:

dlookup([PMONTH], "QryPriorMonthcostofsale", "[Field1] = " & Month([Period]) & " and [Field2] = " & Year([Period]))

Now the criteria expression to select the record is just a generic representation of how to do it. I need to know the field names in the query that you want to select on and where the values for comparison are coming from. Please provide this information and we can finish the Function statement.

Bob Scriver

 

[arus76]

The WHERE criteria would be month and year. The paramater type would be Integer. Eventually I will get this 2 parameters from a form.
Thanks

 

[scriverb]

Below is a shot at giving you what you want. We are using the field PERIOD to select the Month and Year and compare them to the entries on a form with controls that will have a selected Month value and a Year value. I used the CIng function to convert the form controls value to Integer for the comparison to the Month and Year functions which return an Integer.

=DLookup([PMONTH], "QryPriorMonthcostofsale", "Month[PERIOD] = " & CInt([Forms]![frmYourForm]![MonthControlName]) & " and Year[PERIOD] = " & CInt([Forms]![frmYourForm]![YearControlName]))

You will have to update this with the name of your form and control names. Let me know if you need more assistance.



Bob Scriver

 

[CosmoKramer]

Look at the following thread for an alternative method:

thread703-536428

 

[arus76]

setup the following code on the control source of my report and when I run the report using my form I get #ERROR .

=DLookUp("[PMonth]","QryPriorMonthcostofsales","Month[PERIOD]=" & CInt(Forms!Premier!cbMonth) & "and Year([PERIOD])=" & CInt(Forms!Premier!cbYear))

Any other suggestions?
Thanks

 

[scriverb]

What are the uniquely identifiable fields names from your Query "QryPriorMonthcostofsales" that we can use to select the record needed to obtain the value of [PMonth]. At this point we are trying to select the record from this query with the Month and Year of a field called PERIOD. Is Period even a field from this query.

I am asking these questions to verify the criteia expression we are building but getting an error.

Bob Scriver

 

[arus76]

My query fields are:

Pmonth , Month([PERIOD]), Year([PERIOD])
The only field that is show is Pmonth.
Let me know if you need more information
Thanks

 

[scriverb]

From what you just posted I need the SQL from your query posted as well. Just click the SQL button in the upper left and when the SQL window displays the code just highlight it all and paste it in the Thread.

Thanks.

Bob Scriver

 

[arus76]

SELECT Sum(QryCostofsales2.SumOfSumOfMTD) AS PMonth
FROM QryCostofsales2
WHERE (((Month([PERIOD]))=([Forms]![Premier]![cbmonth])) AND ((Year([PERIOD]))=([Forms]![Premier]![cbyear])-1));

 

[scriverb]

As we can see from the above posted query SQL your query is only passing one column PMonth. The fields Month([period]) and Year([PERIOD]) are not being forwarded in this query. For the DLookup to work they must be passed forward so that we may use them to select a record. This is why the DLookUp had no chance of working.

SELECT Sum(QryCostofsales2.SumOfSumOfMTD) AS PMonth, Month([PERIOD]) as PeriodMonth, Year([PERIOD]) as PeriodYear
FROM QryCostofsales2
WHERE (((Month([PERIOD]))=([Forms]![Premier]![cbmonth])) AND ((Year([PERIOD]))=([Forms]![Premier]![cbyear])-1));

Use the above SQL with the updated Red code as the SQL for your query QryPriorMonthcostofsales.

Now use the following DLookup and see if this works:
=DLookUp("[PMonth]","QryPriorMonthcostofsales","[PeriodMonth] =" & CInt(Forms!Premier!cbMonth) & "and [PeriodYear] =" & CInt(Forms!Premier!cbYear))

Bob Scriver

 

[arus76]

After updating my SQL statement and run the query I get the error:
"You tried to execute a query that does not include an specific expression' Month([PERIOD])as part of an aggregate function."

Any suggestions?

 

[scriverb]

Sorry about that. Didn't see the Sum function on the other field. Use this:

SELECT Sum(QryCostofsales2.SumOfSumOfMTD) AS PMonth, Month([PERIOD]) as PeriodMonth, Year([PERIOD]) as PeriodYear
FROM QryCostofsales2
GROUP BY Month([PERIOD]), Year([PERIOD])
WHERE (((Month([PERIOD]))=([Forms]![Premier]![cbmonth])) AND ((Year([PERIOD]))=([Forms]![Premier]![cbyear])-1));

This should work for you now.


Bob Scriver

 

[CosmoKramer]

Just a friendly reminder:

You might want to check out the thread I mentioned above. If you already have the query written to get the total, the subreport method might save you a lot of grief......

 

[arus76]

After updating the SQL code from My query :
SELECT Sum(QryCostofsales2.SumOfSumOfMTD) AS PMonth, Month([PERIOD]) AS PriorMonth, Year([PERIOD]) AS PriorYear
FROM QryCostofsales2
GROUP BY Month([Period]), Year([PERIOD])
WHERE (((Month([PERIOD]))=(Forms!Premier!cbmonth)) And ((Year([PERIOD]))=(Forms!Premier!cbyear)-1));

I get the error: Syntax error (missing operator) in query expression ' Year([PERIOD])
WHERE (((Month([PERIOD]))=(Forms!Premier!cbmonth)) And ((Year([PERIOD]))=(Forms!Premier!cbyear)-1));

How do u create Subreports? Thanks for the advise

 

[CosmoKramer]

Since you already have the query written to calculate the total, create a simple report with no headers, footers, etc. Include only this total field in the detail section.

Insert this report as a subreport where you want it to go in your main report. You can format the font the same as your main report controls to get the one-report feel.

You don't have to link this subreport to any fields on the main report, it can stand by itself.

Let me know if this helps.....