X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> A Query Returning Multiple Rows Based On Field Value, Access 2010    
post Dec 29 2018, 01:02 AM

Posts: 223
Joined: 30-November 10


Season's greetings and best wishes for the new year.

I want a query returning variable rows based on a value in the field. The table is as follows -

TankID Text Primary Key
InnerColumnCount Integer
InnerRafterCount Integer
OuterRafterCount Integer

I am calculating position of both inner and outer rafters through complex trigonometrical calculations. I am successful with spreadsheet solution, but, interested with database.

Can I use something like shown below in a query.

For i = 1 to InnerColumnCount

Perform some Calculations

Next i

And than produce the query with the following fields -

TankID InnerRafterNumber LinearPosition Elevation
XYZ, 1, 12345, 123.456
XYZ, 2, 12349, 123.567
XYZ, 3, 12355, 123.789
XYZ, 4, 12345, 123.890

In above query result, I would have indicated InnerRafterCount as 4 and query is required to returned four records. LinearPosition and Elevation are arrived using very complex formulas.

I can use custom function employing a For loop , but, each record would be stored in a new temporary table with no connectivity to tblRafterCount and any changes in main table would not propagate in my final result.

I look forward to hear from the group members.

Go to the top of the page
post Dec 29 2018, 01:42 AM

Posts: 3,171
Joined: 27-February 09

Can you post your calculation that works in Excel? And maybe a tiny sample of the data you're working with? I can't really help because I need more details. how does your calculation work in Excel?
Go to the top of the page
post Jun 21 2019, 11:01 PM

Posts: 223
Joined: 30-November 10

Sorry for getting back late.

I've attached the file.

In the calculation, the number of calculated rows "Radial distance for rafter" is same as the value of "Count between Columns".

I am not sure, how I can achieve this. Spreadsheet version works excellent where I can add / delete rows as per my desire, however, I don't have that luxury with Access. Also, have a look at the formula. This formula is for the one column. I've four more formulas to handle with.


Attached File(s)
Attached File  Formula.JPG ( 64.99K )Number of downloads: 4
Attached File  RafterCalculation.JPG ( 159.32K )Number of downloads: 4
Go to the top of the page
post Jun 22 2019, 12:41 AM

Posts: 650
Joined: 25-January 16

Screen shots not very helpful. Request was to provide Excel file

Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    19th July 2019 - 12:55 AM