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
> Help With Simple From Control Source, Access 2013    
 
   
Steven Conner
post Aug 30 2019, 02:44 AM
Post#1



Posts: 27
Joined: 9-August 19



Hi Anybody ?

I seek help from anyone who is good at building formulae from expression builder in Form.

I have an problem whereby I wish to have a FORM to show me only the minimum quantity from the records of rows.

Example, I have 7 items that is 108 ( already calculated from queries) , I would like the FORM to only show me the values of "108" which defines the item in the fields.
I would like to know how to get the QTY = 7 as 108 has 7 item that has the same 108.

I do not want to see other value from 623 to 2058 as they are not the minimum value.

From the " Control Source " I wish to build and expression but keep returning #Name?.

Does anyone knows how I can solve. I try to use IFF , DCOUNT ,etc....with no success.

Attached is the picture for you understanding.

Thank you for looking.
Best Regards
Steven Conner
Attached File(s)
Attached File  AAA.JPG ( 125.33K )Number of downloads: 14
 
Go to the top of the page
 
June7
post Aug 30 2019, 03:17 AM
Post#2



Posts: 873
Joined: 25-January 16



ControlSource is the wrong property to use.

You need to apply filter criteria to form if you want to restrict records.

This will be difficult on form. Why do you need on form? Usually this type of filtering would be for output to a report.



--------------------
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
 
arnelgp
post Aug 30 2019, 05:01 AM
Post#3



Posts: 1,463
Joined: 2-April 09
From: somewhere out there...


create another Query (qryMinCycles) based on the query where you get 108 available cycles:

select [part number I], min([available cycles] As MinCycle, count("*") As CountMinCycle
From firstQueryName
group by [part number I], [available cycles]
order by [part number I], [available cycles];

on Qty textbox on your subform:

=dlookup("CountMinCycle", "newQueryNameHere", "[part number I] = '" & [partnumber I] & "'")

on qty on Main form:

=DLookup("MinCycle", "newQueryNameHere", "[part number I] = '" & [partnumber I] & "'")

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
Steven Conner
post Aug 30 2019, 08:56 AM
Post#4



Posts: 27
Joined: 9-August 19



Thank You June7 and arnelgp,

This is for arnelgp

From your SQL statement , where does MinCycles and MinCycle come from or you mean MinAvialableCycles?

I intent to name my new query name as Final.

The fields name list I intent to use are Available Cycles, Part Number 1, Serial Number 1, etc....

Can you help me with the new SQL statement?

I am sorry if I ask dumb questions as I am very new to Access , SQL, VBA and these new terms which I am having a hard times trying to learnt.

Thank You
S Conner

Have a nice weekend......
Go to the top of the page
 
GroverParkGeorge
post Aug 30 2019, 09:18 AM
Post#5


UA Admin
Posts: 35,881
Joined: 20-June 02
From: Newcastle, WA


As is often the case, trying to extrapolate information from screenshots can be a bit difficult.

Can you provide some sample data to work on? Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
arnelgp
post Aug 30 2019, 12:35 PM
Post#6



Posts: 1,463
Joined: 2-April 09
From: somewhere out there...


if you can post the SQL string of the query where you get 108,
then I, or somebody will create the query you need.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
Steven Conner
post Aug 30 2019, 02:17 PM
Post#7



Posts: 27
Joined: 9-August 19



Thanks to all...

I will post the SQL statement of how I get
108 from the source data.

Kindly note that there are always 52 rows of data from the
queries and everyone month, when a new set of raw data is added, the
queries will recalculate and it only sort ascending from lowest to highest.

Example next data may get maybe minimum value of 56 whereby 6 rows have 56 out of 52 rows. or minimal 250 whereby qty is 18 rows has 250 minimum.

So I have gotten the queries format/ calculation all correct but I need
to made a report that shows only the lowest qty only. I do not need to show the other values even though the queries calculated and sort them out.

I will post the SQL for how I got 108 on Monday as now my office is closed.

Thanks to all for the help.
Cheers and have a nice weekend
S Conner.
This post has been edited by Steven Conner: Aug 30 2019, 02:17 PM
Go to the top of the page
 
Steven Conner
post Sep 1 2019, 09:32 PM
Post#8



Posts: 27
Joined: 9-August 19



Hi All

Here is my SQL statement from where I got my 108.

SELECT TOP 52 Sorted.[Available Cycles], Sorted.[Serial Number1], Sorted.[Reg], Sorted.[Component Description], Sorted.Position, Sorted.[Part Number], Sorted.[Serial Number], Sorted.[Date Installed], Sorted.[Part Description], Sorted.[Part Number1], Sorted.Position1, Sorted.TSN, Sorted.CSN, Sorted.[LL)], Sorted.Project

FROM Sorted

GROUP BY Sorted.[Available Cycles], Sorted.[Serial Number1], Sorted.[Reg], Sorted.[Component Description], Sorted.Position, Sorted.[Part Number], Sorted.[Serial Number], Sorted.[Date Installed], Sorted.[Part Description], Sorted.[Part Number1], Sorted.Position1, Sorted.TSN, Sorted.CSN, Sorted.[LL], Sorted.Project

HAVING (((Sorted.[Reg])="CSS") AND ((Sorted.[Component Description])="ENGINE") AND ((Sorted.Position)="1") AND ((Sorted.[Part Description]) In ("PIPE","CUTTER")))

ORDER BY Sorted.[Available Cycles], Sorted.[Serial Number1];

I hope it is not to complicated.

Thank you for viewing.
S Conner.

Best Regards
Go to the top of the page
 
arnelgp
post Sep 2 2019, 02:06 AM
Post#9



Posts: 1,463
Joined: 2-April 09
From: somewhere out there...


same thing.

on Qty textbox on your main form:

=dCount("1", "queryNameFrom108", "[serial number1] = '" & [serial number1] & "' and [part number] ='" & [part number] & "'")

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
Steven Conner
post Sep 2 2019, 03:50 AM
Post#10



Posts: 27
Joined: 9-August 19



Thanks arnelgp

But I think I need the SQL for my queries name which you ask for few days ago.

Here is my SQL statement from where I got my 108. Below is the SQL form where I got my 108 , but there are sorted from min to max, and 108 happens to have 108 ( QTY 7 )
I need it qty 7 to be reflected in my report.

SELECT TOP 52 Sorted.[Available Cycles], Sorted.[Serial Number1], Sorted.[Reg], Sorted.[Component Description], Sorted.Position, Sorted.[Part Number], Sorted.[Serial Number], Sorted.[Date Installed], Sorted.[Part Description], Sorted.[Part Number1], Sorted.Position1, Sorted.TSN, Sorted.CSN, Sorted.[LL)], Sorted.Project

FROM Sorted

GROUP BY Sorted.[Available Cycles], Sorted.[Serial Number1], Sorted.[Reg], Sorted.[Component Description], Sorted.Position, Sorted.[Part Number], Sorted.[Serial Number], Sorted.[Date Installed], Sorted.[Part Description], Sorted.[Part Number1], Sorted.Position1, Sorted.TSN, Sorted.CSN, Sorted.[LL], Sorted.Project

HAVING (((Sorted.[Reg])="CSS") AND ((Sorted.[Component Description])="ENGINE") AND ((Sorted.Position)="1") AND ((Sorted.[Part Description]) In ("PIPE","CUTTER")))

ORDER BY Sorted.[Available Cycles], Sorted.[Serial Number1];


I hope it is not too complicated to ask , however i got some idea about your formulae , but I think I do not have the new queries "Hose"

Thanks you for your help however small it is.

Best Regards,
Steven Conner

Attached File(s)
Attached File  BBB.JPG ( 126.08K )Number of downloads: 0
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th October 2019 - 02:23 AM