UtterAccess.com
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    
 
   
NimishParikh
post Dec 29 2018, 01:02 AM
Post#1



Posts: 223
Joined: 30-November 10



Hi,

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 -

CODE
tblRafterCount
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.

CODE
For i = 1 to InnerColumnCount

Perform some Calculations

Next i


And than produce the query with the following fields -

CODE
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.

Thanks,
Nimish
Go to the top of the page
 
MadPiet
post Dec 29 2018, 01:42 AM
Post#2



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
 
NimishParikh
post Jun 21 2019, 11:01 PM
Post#3



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.

confused.gif

Nimish
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
 
June7
post Jun 22 2019, 12:41 AM
Post#4



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