i normally just go into design view in my query to change the format type, but some of my subqueries are quite large, so i'm getting 'the expression exceeds the 1,024 character limit for the query design grid.
i figured i could just multiply the subquery by 100, but the results are still calculated out to about ten decimal places.
so... how do i format a subquery directly in sql to be a percentage with two decimal places?
Hey i have a percent field in a query which displays correctly in the datasheet view, but when make the report the percent is just a long row of numbers :confused:
anyone got any suggestions? would be greatly appreciated
Maybe someone knows the logic behind MS Access for the display of percents in the formating..... ie.... If you input 3 you get 300.00%. I really dont think ANY user would look at a form... with a field called...say "Commission percent" and expect an input of a 5 to return 500.00% Does this need to be corrected with coding?
I searched everywhere for a solution to my problem. I have to create a field and set its format property to Percent using code. I know how to create the field using code but how do i get to Format property? Is that possible? Thanks in advance for your answers!
We are developing a complaints tool. Each completed complaint needs to be signed off by 3 leads and I'm hoping to display the progress of this in a form. Obtaining the first is simple and I was able to do that relatively quickly. The subsequence ones are now giving me a headache as it doesn't seem I can reference the initial subquery field in the others.Here's what I have so far:
Code: SELECT COMPLAINT_TBL.COMPLAINTID, COMPLAINT_TBL.CASENUMBER, COMPLAINT_TBL.COMPLAINTANTFORENAME, COMPLAINT_TBL.COMPLAINTANTSURNAME, BUSINESSUNIT_TBL.BUSINESSUNIT, COMPLAINT_TBL.FINALRESPONSEDATE, (SELECT TOP 1 [SIGNOFF_TBL].[SIGNOFFDATE] FROM [SIGNOFF_TBL] WHERE [SIGNOFF_TBL].[COMPLAINTID] = COMPLAINT_TBL.COMPLAINTID ORDER BY [SIGNOFF_TBL].[SIGNOFFDATE] DESC) AS FIRSTSIGNOFF,
How can format proper display for percentage values. What happens is I chose a field as percent, but if I put in 9 meaning 9 % I get 900% as the value is multiplied by 100, but if I put in 0.09 I get 0%. I just want to put in day 9 and be saved as 9%.
Hi, in my query I am calculating a NET PRICE starting from the GROSS PRICE and the DISCOUNT % (Percent field). The Net Price shows incorrectly an amount which contains several decimals "invented" at the right end. For example, in this moment I have an item with Gross Price = 576,09 and Discount % = 10,00%. The Discount Amount shown by the query is 57,6090049743652 while the correct result should be 57,609. Therefore the Net Price is incorrect as well. Any thoughts about this ? Is there any known problem with the PERCENT fields ? Thanks and regards. Alberto
This is for the purchasing guy at my client's office. He has 9 vendors he wants to track their on time delivery performance. The data comes from the purchase order tables in his accounting software, to which I've linked via ODBC.
So far so good. I've created a query to pull only those 9 vendors from his total population, then ask for the date range to query by date for the date range (start and end date), and use an expression to calculate the difference between the date wanted and date received so he can tell if the delivery was on time or not. (ex: date wanted: 3/10/06 date received 3/13/06. 3 days difference = late delivery)
The twist is he wants another field so he can mark whether he considers the delivery late or not depending on extenuating circumstances he keeps locked up in his cranium, despite what the date difference results tell him. So, I created a combo box and he can select "Yes" or "No." If he selects YES, a value of 1 is written to the table in that field, if he selects NO, a value of 2 is written to the same field.
So, I need the query to group by vendor, count the total number of entries for that month by that vendor, count the number of "1"s and express that total as a percent of total deliveries.
To summarize, vendor A had 8 deliveries, 5 were on time, meaning he had a 62.5% delivery score. Vendor B had 10 deliveries, 7 on time, meaning a 70% delivery score.
Sorry for the long winded explanation, but perhaps someone can assist. All help appreciated.
I have a a table that stores various financial information such as sales receipt totals and variance totals (if actual cash in drawer did not match receipts, etc...) that I use to track cashier performance and identify possible problems. Part of this process includes a query that I pull reports against.
One such query, simplified to illustrate the concept, lists the dollar total that their receipts indicate they made, and the dollar amount that their actual drawer was off (either short or over what they should have taken in.) In this query I added fields that total Netsales (calculated from the first two fields) and another that calculates the percentage the variance is compared to their NetSales. The SQL behind the query is as follows:
Code:
SELECT tbl_OSRImport.Receipts, tbl_OSRImport.OverShort, [Receipts]-[OverShort] AS NetSales, [OverShort]/[NetSales] AS VarPerc FROM tbl_OSRImport WHERE (((tbl_OSRImport.OverShort)<>0) AND (([Receipts]-[OverShort])<>0));
This query works just fine. The calculated fields correctly display their results. The issue presents itself when I try to build a method for a user to run a report to see all the cashiers whose Varience Percentage (VarPerc) is equal to or within a range they specify. This allows the user to see all the cashiers who, for example, are more that 5% over or short. I have tried a number of criteria expressions in the query, with no success. I have gotten everything from a prompt asking me for paremeters to an error stating "Stack Overflow." I believe the problem has something to do with the fact that the numerical value that is calculated is a long string of numbers ending in letters and characters, which the Query displays as a neat and tidy Percentage. Below is an example of the data that I hope will explain this:
In the Query, the expression is: VarPerc: [OverShort]/[NetSales]
When the Query runs, the full numerical result is: -4.27103159497526E-02
Which visually is output as: -4.72%
Mathmatically (on a calculator using the same values) the equation is as follows:
-11.22 / 262.70 = -0.04271032
I think my attempts are failing becaue the query is trying to compare the user's input of (for example) 5, .5, .05, etc.... against the numerical value in the query result that includes the E-02 (above example.) So, rightfully it comes back with no results or an error.
I'm enrolling medical centers in a new multi-center database. There are a number of steps that must be completed before the center is enrolled in the project. I have a form that serves as a checklist. I want the textbox at the bottom of the form to display the percentage of steps completed. I guess you would have to count the number of fields on the form that are filled in, but I don't know how to do it.
Can anyone tell me what is wrong with this complex query? It works fine without the highlighted subquery. The subquery works fine on its own. But they don't work together.
I think that I might not be able to call multiple results from a subquery but I'm not sure about that. Does any one know???
Any help will be much appreciated! Thanks! select k.site_id, s.name, t.date_opened,
(select concat(c.name_first,' ', c.name_last) as User from sitepack_approval sa left join sitepack_approval_users spa on sa.approval_id=spa.approval_id left join contacts c on spa.user_id=c.contact_id where sa.site_id=k.site_id and spa.timestamp is null order by spa.level limit 1) as pending_approver,
(select sat.name from sitepack_approval sa left join sitepack_approval_types sat on sa.approval_type_id=sat.approval_type_id where sa.site_id=k.site_id order by sa.approval_id desc limit 1) as sitepack_type,
(select t.site_id, D.name AS Division, R.name AS Region, M.name AS Market, concat(C1.name_first, " ",C1.name_last) AS RD, concat(C2.name_first," ",C2.name_last) AS DD FROM stores as t LEFT JOIN regions as M ON t.region_id = M.region_id LEFT JOIN regions AS R ON M.parent_region_id = R.region_id LEFT JOIN regions AS D ON R.parent_region_id = D.region_id LEFT JOIN contacts AS C1 ON R.contact_id = C1.contact_id LEFT JOIN contacts AS C2 ON D.contact_id = C2.contact_id) as site, Divsn, Reg, Mkt, RDs, DDs,
concat(cd.name_first,' ',cd.name_last) as 'DRE'
FROM sitepacks k LEFT JOIN sites s on k.site_id=s.site_id LEFT JOIN stores t on k.site_id=t.site_id LEFT JOIN contacts cd on k.dre_contact_id=cd.contact_id WHERE status_id=(select status_id from sitepack_status where description='Approving' and sitepack_type <> 'Renewal') and t.status_code <> 'DEAD' and t.date_opened > now();
Hello I have had a bit of a stumbling block with a subquery for a Daily production report and I was hoping someone could advise me.
An initial query presents the information as the table below.
Date ProdNoFirstOfSerialNoLastOfSerialNo Qty 13/11/2006004006P064600254P06460028128 13/11/2006004006P064600282P06460030019 13/11/2006004105P064600301P0646003033 13/11/2006004006P064600304P06460031714 13/11/2006004006P064600318P06460034427
However what I need to have is the table actually look like this,
Date ProdNoFirstOfSerialNoLastOfSerialNo Qty 13/11/2006004006P064600254P06460030047 13/11/2006004105P064600301P0646003033 13/11/2006004006P064600304P06460034441
I had thought to try and make another query identify the change of product number to then set the first and last serialNo's but I have no idea on how to write the statments necessary. If anyone could offer some advice it would be very much appreciated.
I haven't used subqueries before, so I am not 100% on the best way to approach this.
The Scenario:
I have tables;
tblJobDetails tblMissingKit tblSiteDeficiencies
JobID is the primary key for tblJobDetails. tblMissingKit and tblSiteDeficiencies are both tables containing (funnily enough) Missing Kit and Site Deficiences on the Job's (JobIDs) in tblJobDetails. The problem is, there can be more than 1 amount of missing Kit per JobID and more than 1 site deficiency per JobID.
In an ideal world, when I run this query, I would only get 1 record per JobID, but in a 'MissingKit' field, there would be a list of the missing kit for that site (new lines, comma seperated - doesn't matter) and then the same with Site Deficiencies.
Not sure if this is even doable, but thought I'd ask the people who know.
2) Can the parameter value be the value from another field in the query?
In a nutshell, I want to write a query that returns these fields about some marbles:
Color of Marble Marble Stat1 Marble Stat2 Marble Stat3 Number of Marbles of this Color
i.e. desired query results
Blue Stat1 Stat2 Stat3 24 Red Stat1 Stat2 Stat3 12 Green Stat1 Stat2 Stat3 38
The query is fine except for the last field, Number of Marbles of this Color. This field is a subquery with a parameter that is the color of the marble. So if I run the subquery on its own and pass 'blue' as the parameter, it returns 24.
Here is the SQL for the subquery, qry_Count_Marbles:
SELECT Count([Marble_ID]) AS [Count] FROM tbl_Marble_Info WHERE (((tbl_Marble_Info.COLOR)=[Marble_Color]));
Marble_Color is a parameter, and If I enter 'blue' it returns 24.
In my main query, I am trying to pass the parameter for the subquery as the result of the first field. It's not working, but here is my SQL:
SELECT tbl_Marble_Guidelines.COLOR, tbl_Marble_Guidelines.LOW, tbl_Marble_Guidelines.HIGH, tbl_Marble_Guidelines.FUND, qry_Count_Marbles.Count(tbl_Marble_Guidelines.COLO R) FROM tbl_Marble_Guidelines, qry_Count_Marbles
I'd like the main query to not prompt the user for the parameter, but simply grab the parameter value from the value of the first field. Is what I'm trying to do possible? Is there a way to pass a SQL parameter within the SQL itself, and not prompt the user?
I am trying modify my forms so that I can display data like a tree view. I have a mainform and a subform. The source of the subform is a datasheet view of a query which has children linked to it. I have attached a snapshot view to this post. How do I access a field in the child in the subform (it is not a subsub form - it is a subquery within a subform)? (Field need to be accessed highlighted in black in th snapshot)
Ok at the moment, my sql statement works. However the return results is not arranged according to the earliest time. I've set DATATYPE for my Time as TEXT instead of DATE/TIME. This is because of other limitations in my forms.
Ok, that aside. This is how my SQL looks like:
Code:Select DISTINCT Time from Sample where Lot_No = 'BB55555' AND Product_Code='1234567'
Is there any way where by I can include Sample_id so that I can sort the Results using Order by Sample_id ASC?
i tried this but the DISTINCT doesnt work anymore.
Code:Select DISTINCT Time, Sample_id from Sample where Lot_No = 'BB55555' AND Product_Code='1234567' ORDER BY Sample_id ASC
I have written a subquery that works fine alone, but it returns -1 when with query.
The subquery is supposed to return a total of type currency.
Any help/suggestions will be very much appreciated.
SELECT tblSite.Name, tblPhase.Phase_No, tblVariation_Order.Customer_No, Exists (SELECT Sum(tblVariation_Order.VO_Price) FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No GROUP BY tblCustomer.Customer_No;) AS Expr1 FROM (tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) INNER JOIN (tblSite INNER JOIN (tblPhase INNER JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No) ON tblCustomer.Customer_No = tblHouse.Customer_No WHERE (((Exists (SELECT Sum(tblVariation_Order.VO_Price) FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No GROUP BY tblCustomer.Customer_No;))=True));
well here's my problem, i've been working on this homework for about 3 days i got most of my queries work except for the 2 that use nested or subqueries, i have 2 tables http://img251.imageshack.us/img251/4623/untitledko5.png everytime i run the query i get this http://img251.imageshack.us/img251/7645/untitled2rq0.png i want the query to show me the costumers that have not made any orders, basically it should show me ID #6 no order,
when i use SELECT Costumer.ID, Costumer.fname, Costumer.lname FROM Costumer INNER JOIN [Order] ON Costumer.ID = Order.tblCostumer_ID WHERE (((Costumer.ID) Not In ([Order].[ID])));
it returns the above pic of a blank query
this is what my POS book says i should type:
SELECT Costumer.ID, Costumer.fname, Costumer.lname FROM Costumer WHERE ID NOT IN (SELECT ID FROM Order);
but everytime i try that i get a syntax error after the NOT IN.
i don't know what else to try if anyone could help me out it would be greatly appriciated.
The following query works fine: SELECT tblProjectSteps.ProjectID, tblProjectSteps.StepID,..., NZ(DSum("Total","tblPayments","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0)+ NZ(DSum("Total","tblReceivables","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0)+ NZ(DSum("Total","tblBankPayments","ProjectID=" & [ProjectID] & " AND StepID =" & [StepID],0) AS TotalCosts FROM tblProjectSteps INNER JOIN tblSteps ON tblProjectSteps.StepID = tblSteps.StepID;
The 3 summed tables (tblPayments,tblReceivables,tblBankPayments) do all of course have a foreign key for ProjectID (from tblProjects) and StepID (from tblSteps) and the output would be something like
For performance issues I would want to eliminate the DSum part, i.e get me a TotalCosts field by other means . I tried several constructions with multiple queries and also subqueries but to no avail. Any suggestions from the query Cracks her would be greatly appreciated.
Hi, we are currently working on a project to strip down one of our SQL Server based applications to an Access 2007 based solution, and I met this problem: the query just doesn't work on the new Access mdb. Can anyone take a look and give some help? Thanks a lot.
UPDATE A SET MaxPrice= B.MaxPrice FROM Summary A INNER JOIN (SELECT Name, MAX(DailyPrice) AS MaxPrice FROM Data GROUP BY NAME) B ON a.Name = B.Name
Is it possible to use a subquery as a default value property setting?
I need to use a value from a table that is not the focus table of the form, I have tried to identify it with a subquery but it returns a #name? error every time. I hate those!
the following query works pretty much well: SELECT teamid, teamname, (select count(winlose) from winlose where team.teamid=winlose.teamid and winlose='win') as win, (select count(winlose) from winlose where team.teamid=winlose.teamid and winlose='lose') as lose, (win*2)+(lose*1) as pts FROM team ORDER BY teamname
but the problem arises when i want to sort the report based on the "pts" which is the result of calculated two subqueries ("win" and "lose"), because i can't use the "ORDER BY pts" as access cannot order records by alias. can anybody find another sql solution to this?
theoritically i can just put all the records in an array and sort it by pts field, but sorting multidimensional with numerical and text array in asp kinda flakey at best, so i hope i can find the solution in the sql. thank you all in advance.
Code: (SELECT SUM(Cash) FROM qryAccountSetup AS T2 WHERE T2.ClientID = [qryAccountSetup].ClientID AND T2.MonthlyDate < [qryAccountSetup].MonthlyDate) AS PriorCash,
How do you change this to allow multiple values. I looked up subqueries online and it said to nest a second sub for the where clause....
Code: (SELECT (Cash) FROM qryAccountSetup AS T2 WHERE (Select (SELECT MAX(T3.MonthlyDate) FROM qryAccountSetup AS T3 WHERE T3.ClientID = qryAccountSetup.ClientID AND T3.MonthlyDate < qryAccountSetup.MonthlyDate)) AS PriorCash, But i keep getting error.
What i am trying to do is get the previous cash value.... For now, a good indicator is MonthlyDate, as only one date per client will exist. Something can only happen once in a day....IE.
ClientID..MonthlyDAte 1...........03/01/2013 1...........31/01/2013 1...........01/02/2013 1...........and so on.
There can be multiple dates, however the clientID would not match then.... Does this make sense? There is also a PositionID... This is an autonumber field in its respective table.. I have that so i can use it for domain purposes in the future.
This db I'm working on tracks the latest revisions of drawings. Multiple drawings fall under a work package, and multiple revisions of a drawing are present (listed 1, 2, 3, ...). I want to pull information about the latest revision of a drawing, including the drawing number, the tracking ID from the tbl_wkpkg_dwg_rev table, and the corresponding dwgTitle from tbl_dwg. The tables are joined through the field wkpkg.
I have my select statement partially working; it works on one table. This is what it returns: dwg1 3 id1 dwg2 1 id2 dwg3 5 id3
Here is the statement:
SELECT r.dwg, r.revision, r.trackingID FROM tbl_wkpkg_dwg_rev r, (SELECT MAX(revision) as maxrev, dwg FROM tbl_wkpkg_dwg_rev GROUP BY dwg) MaxResults WHERE r.dwg = MaxResults.dwg AND r.revision = MaxResults.maxrev;
So I would like to join it to tbl_dwg and get: dwg1 3 id1 DrawingTitle1 wkpkg1 dwg2 1 id2 DrawingTitle2 wkpkg1 dwg3 5 id3 DrawingTitle3 wkpkg2
However, when I try and join it with tbl_dwg to get the other information, it returns this: dwg1 3 id1 DrawingTitle1 wkpkg1 dwg1 3 id1 DrawingTitle1 wkpkg1 dwg1 3 id1 DrawingTitle1 wkpkg1 dwg1 3 id1 DrawingTitle1 wkpkg1 dwg2 1 id2 DrawingTitle2 wkpkg1 dwg2 1 id2 DrawingTitle2 wkpkg1 dwg2 1 id2 DrawingTitle2 wkpkg1 dwg2 1 id2 DrawingTitle2 wkpkg1 dwg3 5 id3 DrawingTitle3 wkpkg2 dwg3 5 id3 DrawingTitle3 wkpkg2 dwg3 5 id3 DrawingTitle3 wkpkg2 dwg3 5 id3 DrawingTitle3 wkpkg2 dwg3 5 id3 DrawingTitle3 wkpkg2
I tried using SELECT DISTINCT, with no luck. I'm out of ideas, so any help would be appreciated! :confused:
