how to avoid cascade queries (1 Viewer)

smile · Oct 27, 2008

Hi, I have a database where I need to

1. group items by group and date,
2. then calculate total.

I do this using 2 queries. Since I need to create 50 groups or so that means 100 queries.

I would be pleased if somebody showed me away to cut it in half or other method to do this.

How can do the same without using cascade queries? If it possible to use only 1 query that means I need 50 queries not 100

See attached db.

BTW report grouping does not work for me as I need "fixed" style report where every month or so you can see even groups with zero in them. I tried to use report groupings but they showed me only "active" records and groups that were not used within a month were now showed.

Banana · Oct 27, 2008

One way would to be write a subquery within the query.

I would gladly provide you the SQL but I do not have Access present on the computer so I can't look at it. Maybe someone else can help you.

Brianwarnock · Oct 27, 2008

I have looked at the DB but am confused as to what is required.
The form allows me to select a date range which is used in query1 to select the records to groupby date and groupid and sum the costs. query2 just sums the sums for a one line result.

What are the 50groups and what is all that stuff in your BTW about?

Brian

smile · Oct 27, 2008

Brianwarnock said:
I have looked at the DB but am confused as to what is required.
The form allows me to select a date range which is used in query1 to select the records to groupby date and groupid and sum the costs. query2 just sums the sums for a one line result.

What are the 50groups and what is all that stuff in your BTW about?

Brian

I will have about 50 codes I enter into tbl_items, they group information.
The codes will be General Ledger Account Codes.
Example here: http://www.ofm.wa.gov/policy/75.40.htm

Updated db example see tables: tbl_code and tbl_items
Query group code total_code
Report rpt_total_code - this is what I need my report to look like but now my fields are in detail section and autopopulate. I need to have them in header section etc. They need to be unbound boxed and have names so I could calculate between them in my report etc.

For this to form I have to make 2 queries for each "code".
If I have 50 codes that makes 100 queries.

Explanation how the earlier posted db works:

The query 1 called "group_grcode" groups records from table "tbl_items" related to tbl_grcode. Records a filtered by date range and sum is calculated.

Because we group by range to get total sum for a month one needs to group records by related field called "group" in tbl_items the calculate sum. Total sum is calculated by query "total_grcode".

The total sum is calculated for a range of dates entered to form "frm_daterange".

Banana · Oct 27, 2008

Sounds like what you really need is a parameter query and a form to enter the criteria to run a query. That way you only need one query but can enter any code and date you need.

Search the forum on parameter query and search criteria form.

Brianwarnock · Oct 27, 2008

Hi Banana

He has a txtbox on his date form called code which if he used it, which he doesn't, would enable him to use just 2 queries to achieve what he appears to want, and using a select and overall total query seems no hardship.
But I'm far from convinced that I understand what he wants, I must be getting old.

Brian

Banana · Oct 27, 2008

Sounds like we're in agreement- he has the form already, so he just needs to link the query to that textbox.

Smile, to do so, use the 'Build...' option from right-click menu in the query view; it should bring up the expression builder and you can select the appropriate field from your form, then as Brian said, you only need two query. You don't have to hardcode the code/dates for each query.

(Brian, thanks for sharing!

)

smile · Oct 27, 2008

Banana said:
Sounds like we're in agreement- he has the form already, so he just needs to link the query to that textbox.

Smile, to do so, use the 'Build...' option from right-click menu in the query view; it should bring up the expression builder and you can select the appropriate field from your form, then as Brian said, you only need two query. You don't have to hardcode the code/dates for each query.

(Brian, thanks for sharing! )

Sorry for my bad English. It’s not my native language.

I will try to explain again:

I have updated the db to use code field. That allows to filter query to calculate total for entered date range and code. However that uses 2 queries. I need 1 query.

Form
frm_daterange - is used to filter between dates and I added code field to it.

Table
tbl_code – is used to enter account codes

Query
group_code - is used to group entries by date and code. Filters results by:

Between [forms]![frm_daterange].[startdate] And [forms]![frm_daterange].[enddate]
[forms]![frm_daterange].[codebox]

total_code – calculates total for entered code.

To achieve this we use
1. Form
2. 2 Queries

I need to do the same with 1 query. I need to have unbound checkbox on my report that I “connect” to query that calculates data for that field. By doing so I can for example sum certain fields on my report by entering their names and I place unbound check boxes on header or other not auto populated part of the report.

Why? Because I have 50 or so account codes. To view report of all codes on one sheet I need to have 2 queries filtering data calculated for each code. That means 100 queries with hardcoded criteria as code.

Isn’t is possible to use Dsum and Dlookup formulas to make a query

1. Group by date
2. Group by code
3. Sum “Price field”

So I image I need some code to have 1 field in a query that would do this.

Brianwarnock · Oct 27, 2008

Some items with say code 1110 will have one date and some another, if you group by date each group of date and code will have its own total, do you want several lines for each code? I thought that you wanted the total for 1110 within the date range. The report in db2 did not show a date, I haven't copied the 3rd example.

Brian

smile · Oct 27, 2008

Brianwarnock said:
Some items with say code 1110 will have one date and some another, if you group by date each group of date and code will have its own total, do you want several lines for each code? I thought that you wanted the total for 1110 within the date range. The report in db2 did not show a date, I haven't copied the 3rd example.

Brian

I want total for each code. Since I group for say a month. If I have 10 codes in my table I must have total for each code.

1. I group by date that is stored in tbl_grcode. (this works trough a lookup, I assign entries from tbl_grcode - think of it as cash receipts etc. to items in tbl_items.)
2. I calculate price total for items in tbl_items by field named "price"

1st cascading query that groups is group_code
2nd cascading query that totals is total_code

You must fill the form then open queries for them to work.

I think you understand that I have done only example what I need. This db does not have hardcoded queries I want. I use a form to enter criteria - so it would be easy to test.

Please download new version as it has code field working for queries I mentioned above.

Brianwarnock · Oct 28, 2008

I am still having trouble in understanding exactly what you require, so I have produced a report as a starting point. It produces a total, each on a new page, for all codes within a date range, It uses the frm_daterange but does not require the code field.

Brian

smile · Oct 28, 2008

Brianwarnock said:
I am still having trouble in understanding exactly what you require, so I have produced a report as a starting point. It produces a total, each on a new page, for all codes within a date range, It uses the frm_daterange but does not require the code field.

Brian

If you enter start date 01/10/2008 and end date 31/10/2008 it shows a report of codes for that period but does not show codes that did not have any transactions during that time. I need to show all codes.

Also you use auto population. The codes are in detail section of the report. As far as I know this means they do no have individual names because they are autopopulated.

Because of this I can't sum any code between themselves and add total field etc.
I mean not make simple right click and click sum but put unbound textbox and enter:

=sum textbox 1 + textbox2
=sum textbox 2 + textbox3 + textbox7.

Banana · Oct 28, 2008

If you want to display all codes, use Brian's query and right join to the lookup table (it's a left join if you select the lookup table first then Brian's query- you want all records from lookup and the matching rows from Brian's query) listing all codes, and add Nz() argument for Brian's query's rows so if there's a Null, it will be shown as a zero.

You can then use that query as the recordsource of the report and it will print all codes.

Brianwarnock · Oct 28, 2008

Thanks for that Banana, of course he must remember to select the codes from the table not the query and there will be other changes, and to the report also, however I don't think it is going to help him, it is the last part of his post that presents the problem

Also you use auto population. The codes are in detail section of the report. As far as I know this means they do no have individual names because they are autopopulated.

Because of this I can't sum any code between themselves and add total field etc.
I mean not make simple right click and click sum but put unbound textbox and enter:

=sum textbox 1 + textbox2
=sum textbox 2 + textbox3 + textbox7.

I guess he wants to change the report on the fly, say decidiing to add the totals of the totals of codes 1110 and 1120.

I will now retire and hope somebody else can advise.

Brian

smile · Nov 11, 2008

1. should I convert number to words in same total query or make seperate query that converts to words?

1st query groups data
2nd query totals it
3rd query convert to words

OR

1st query groups data
2nd query totals it AND convert to words (as expresion)

2. should I convert to words in my report by placing bound total fields from query2 and then unbound textbox with convert to words code?

or shoud I make convertion in query and do not use unbound textboxes with code because they can fail?

smile · Nov 23, 2008

still looking for a sollutions.
Related thread http://www.access-programmers.co.uk/forums/showthread.php?t=161096

Thanks for your time.

how to avoid cascade queries (1 Viewer)

smile

Registered User.

Attachments

Banana

split with a cherry atop.

Brianwarnock

Retired

smile

Registered User.

Attachments

Banana

split with a cherry atop.

Brianwarnock

Retired

Banana

split with a cherry atop.

smile

Registered User.

Attachments

Brianwarnock

Retired

smile

Registered User.

Brianwarnock

Retired

Attachments

smile

Registered User.

Banana

split with a cherry atop.

Brianwarnock

Retired

smile

Registered User.

smile

Registered User.

Similar threads

Users who are viewing this thread