need help with numbering a query (1 Viewer)

jurekorla

Registered User.
Local time
Today, 22:16
Joined
Jan 21, 2015
Messages
14
Hello


i have a report based on query, where i have to join results in groups of 3
I cant sort the results in report according to date (ascending)

although in the query everything seems fine - see pics


I used a module for row numbers i found online but seems its too complicated for me:banghead:


thanks!
 

Attachments

  • rsz_query.jpg
    rsz_query.jpg
    93.4 KB · Views: 49
  • rsz_report.jpg
    rsz_report.jpg
    92.9 KB · Views: 45
  • rsz_module.jpg
    rsz_module.jpg
    93.1 KB · Views: 42

June7

AWF VIP
Local time
Today, 13:16
Joined
Mar 9, 2014
Messages
5,423
Don't understand the sorting and grouping criteria for report in image. Don't need VBA to calculate row number in report. Use textbox RunningSum property. Only available in report.

Post the query SQL statement.

If you want to provide db for analysis, follow instructions at bottom of my post.
 

jurekorla

Registered User.
Local time
Today, 22:16
Joined
Jan 21, 2015
Messages
14
Hello


i just tried Runningsum, the numbers are fine and i can order the data by the date,

but now i cant join results in groups of 3 (i can only choose from query fields ).


i need to group data in groups of 3, calculate average of those 3 results, while all the results together are sorted by date in ascending order



Jure
 

Attachments

  • querysql_opt.jpg
    querysql_opt.jpg
    87.2 KB · Views: 26
  • reportdesignview_opt.jpg
    reportdesignview_opt.jpg
    96.5 KB · Views: 29

jurekorla

Registered User.
Local time
Today, 22:16
Joined
Jan 21, 2015
Messages
14
zipped database
 

Attachments

  • kontrola proizvodnje EN 206 – kopija.zip
    874.9 KB · Views: 45

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:16
Joined
Feb 28, 2001
Messages
27,001
Number the records, then use a query that creates a grouping number.

I.e. build a number field (LONGNUM, perhaps), then number it sequentially, then in a query use

Code:
SELECT ( 1 + ( LONGNUM / 3 ) ) AS GRPNUM, fieldA, fieldB, etc. FROM TheTable ORDER BY GRPNUM ;

which gives you groups of three records with the same GRPNUM. Unless you are going to do some sort of aggregates, you don't actually want a GROUP BY because that kind of forces your hand towards SQL Aggregate usage.
 

June7

AWF VIP
Local time
Today, 13:16
Joined
Mar 9, 2014
Messages
5,423
Gave this a try. Make sure LONGNUM sequence starts with 0.

The LONGNUM calc can be done with subquery or DCount(). Common topic. Here is one. https://stackoverflow.com/questions/17279320/row-numbers-in-query-result-using-microsoft-access

I can actually calculate these values in textboxes in report detail section but can't build a group section. Referencing textbox with the RunningSum calc won't work. Will have to do the LONGNUM count in query so there is a field for the report to reference.
 
Last edited:

jurekorla

Registered User.
Local time
Today, 22:16
Joined
Jan 21, 2015
Messages
14
Hello


im still working on rownumbers. I think im quite close to the solution now. I would like to use something like


SELECT (SELECT COUNT(t1.SourceID) FROM [SourceTable] t1 WHERE t1.SourceID<t2.SourceID) AS RowID, t2.field2, t2.field3, t2.field4, t2.field5 FROM SourceTable AS t2 ORDER BY t2.SourceID;
i understand the source table is once named t2 (in main query) and the other time t1 (in subquery). Using the original name of query in the sentence (lets say qrytest) instead of t2 doesnt work.

Having query based on multiple tables, where should i enter the name like (sourceTable AS t2) in the sql sentence?



btw: the problem of sorting of the data was caused by using module, removing it, everything is ok
 

June7

AWF VIP
Local time
Today, 13:16
Joined
Mar 9, 2014
Messages
5,423
Not sure why you are using a subquery. Did you try TDM's suggested query with the calculation? Certainly not seeing it in your post.

I looked at your db but language barrier gets in the way. Identify report and query involved.
 

jurekorla

Registered User.
Local time
Today, 22:16
Joined
Jan 21, 2015
Messages
14
Hello


[FONT=&quot]i [/FONT][FONT=&quot]tried [/FONT][FONT=&quot][FONT=&quot]to en[/FONT][/FONT][FONT=&quot][FONT=&quot]ter a field in query as follows[/FONT][/FONT]


izr4: DCount("*";"[sourcetbl]";" sourcetbl.IDfield<=" & [sourcetbl].[IDfield])


bu[FONT=&quot]t i[/FONT][FONT=&quot][FONT=&quot]t[/FONT] re[/FONT][FONT=&quot]turns [/FONT][FONT=&quot]only [/FONT][FONT=&quot][/FONT][FONT=&quot]the[/FONT][FONT=&quot] IDs selec[/FONT][FONT=&quot][FONT=&quot][/FONT][FONT=&quot]ted by query[/FONT],[/FONT][FONT=&quot][/FONT]
[FONT=&quot][FONT=&quot]
[/FONT][/FONT][FONT=&quot][FONT=&quot][/FONT][/FONT]

[FONT=&quot][FONT=&quot][/FONT][/FONT]
i would like [FONT=&quot]t[/FONT]o have "renumbered" IDfield wi[FONT=&quot][/FONT][FONT=&quot]t[/FONT]h exclusion of resul[FONT=&quot][/FONT][FONT=&quot]ts no[/FONT][FONT=&quot][/FONT][FONT=&quot]t included in query, bu[/FONT][FONT=&quot][/FONT][FONT=&quot]t s[/FONT][FONT=&quot][/FONT][FONT=&quot]till sequen[/FONT][FONT=&quot][/FONT][FONT=&quot]tially numbered 1,2,3,4,5,...[/FONT]

[FONT=&quot][/FONT]
in [FONT=&quot][/FONT][FONT=&quot]the examples i found [/FONT][FONT=&quot]this was only achieved by renaming [/FONT][FONT=&quot][FONT=&quot][/FONT][FONT=&quot]t[/FONT]he query some[/FONT][FONT=&quot][FONT=&quot][/FONT][FONT=&quot]t[/FONT]hing like "[/FONT][FONT=&quot][FONT=&quot][/FONT][FONT=&quot]t2[/FONT]" and [/FONT][FONT=&quot]using a subquery. I don[/FONT][FONT=&quot][/FONT][FONT=&quot]t know wha[/FONT][FONT=&quot][/FONT][FONT=&quot]t [/FONT][FONT=&quot][/FONT][FONT=&quot]to do/how [/FONT][FONT=&quot][FONT=&quot][/FONT][FONT=&quot][/FONT][FONT=&quot]t[/FONT]o rename, since my query is based on mul[/FONT][FONT=&quot][/FONT][FONT=&quot]tiple [/FONT][FONT=&quot][/FONT][FONT=&quot]tables[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot][/FONT][FONT=&quot]the query and repor[/FONT][FONT=&quot][/FONT][FONT=&quot]t in ques[/FONT][FONT=&quot][FONT=&quot][/FONT][FONT=&quot]t[/FONT]ion are loca[/FONT][FONT=&quot][FONT=&quot][/FONT][FONT=&quot]t[/FONT]ed under[/FONT]
[FONT=&quot]Porocila/Porocilo o [/FONT][FONT=&quot]zace[/FONT][FONT=&quot][FONT=&quot][/FONT][FONT=&quot]t[/FONT]ni proi[/FONT][FONT=&quot]zvodnji - XF4[/FONT]

[FONT=&quot][/FONT]
query and repor[FONT=&quot][/FONT][FONT=&quot][/FONT][FONT=&quot]t can be opened by clicking on bu[/FONT][FONT=&quot][/FONT][FONT=&quot][/FONT][FONT=&quot]t[/FONT][FONT=&quot][/FONT][FONT=&quot][/FONT][FONT=&quot]tons[/FONT]

[FONT=&quot][/FONT]
i also a[FONT=&quot]t[/FONT][FONT=&quot]tached a sample da[/FONT][FONT=&quot]tabase found online wi[/FONT][FONT=&quot]th solved problem bu[/FONT][FONT=&quot]t i can[/FONT][FONT=&quot]t find a way [/FONT][FONT=&quot][FONT=&quot]t[/FONT]o copy i[/FONT][FONT=&quot]t in[/FONT][FONT=&quot]to my da[/FONT][FONT=&quot]tabase (hope i[/FONT][FONT=&quot]t will help someone)[/FONT][FONT=&quot][/FONT]
[FONT=&quot][/FONT][FONT=&quot][/FONT][FONT=&quot][/FONT][FONT=&quot][/FONT][FONT=&quot][FONT=&quot][/FONT][/FONT][FONT=&quot][/FONT][FONT=&quot][/FONT]
 

Attachments

  • DCount calc.jpg
    DCount calc.jpg
    92.8 KB · Views: 21
  • RowNumbering.accdb
    1.5 MB · Views: 43

June7

AWF VIP
Local time
Today, 13:16
Joined
Mar 9, 2014
Messages
5,423
Try:

izr4: DCount("*";"[sourcetbl]";"IDfield<" & [IDfield])

But use your real table/query/field names.

The language barrier is too much for me. You need to start with a much simpler query. When you get the row numbering to work by whatever method, then expand the query design.
 

Users who are viewing this thread

Top Bottom