Queries :: Sum All Alternatives From Column That Equal Specified Value
Jun 27, 2014
I have a database with with 100s of values for a field. What I would like to be do is specify a value via a form and the query will return all options that equal the specified value.
i create a project for salary company , i have a list box and have 6 columns & i have 1 textbox , i work with code and a listbox is the query know i need the value of textbox is equal with the column 1 from listbox how to do it ? like when i run form need to be enter the code when entered the code in listbox is equaled with the textbox .another question , in the list box i have one column it is about salary i want the total of salary.
I have a large table with many fields and many rows. There is no primary key. I'll call one field ParentPN, and another field ChildPN. There are many other fields as well. I want to identify all rows where BOTH the ParentPN and ChildPN occur more than once. I know how to create a query to identify duplicates of ONE field in the table, but not two. I can solve this with VBA: I will read the two fields of interest in the first row, then compare both values with every other row. If it finds another row with BOTH ParentPN and ChildPN identical with the first, that's a "hit". Then, repeat with all the other rows. I could find ways to make this run faster, but I was wondering if there are any build in functions to accomplish this. I looked at the Find Duplicates query builder, and all I see is I can select ONE field to search for dupes, not two.
I have two date columns in my table called "End date" and "Closing date".
An example could be 14-06-2015 and 13-04-2017.
I need to make a query which is checking if the two dates are equal to the last day of their respective month. I don't have two columns in the table with the last day of month, so I first need to find out what the last day in the month is.
I am trying to delete a record in tblinclude where record from tblexclude are equal to clientid and codeid
Here is the sql DELETE tblinclude.ClientID FROM tblexclude INNER JOIN tblinclude ON (tblexclude.ClientID = tblinclude.ClientID) AND (tblexclude.CodeID = tblinclude.CodeID) WHERE (((tblinclude.ClientID)=1));
I get the error Specify the table containing the records you want to delete. I've searched for this but I am just not getting it today.
I have a query based around a persons sick start and return date. A report is based upon this and then this makes up a sub part of management information report that is linked through a formatted month. Part of my report is based around the following formula, this governs which MI report to include it in
This states that if a person has not returned to work then give them a formatted version of now() so it can be linked to the main report. My problem is if i wanted to look at a previous months report and a person has still not returned to work they will have the now() date and thus not be included in a previous months report! Does this make sense, have i gone a really out of the way route to solve my problem and can anyone see a more effective way of carrying out this problem? thanks in advance, sorry for confussion!
SELECT planten.PLANTNAAM, planten.SOORT FROM PLANTEN AS planten WHERE (((planten.ART_CODE) Not In (SELECT offertes.ART_CO FROM OFFERTES offertes, BESTELRG bestelrg, BESTEL bestel WHERE offertes.ART_CO_LEV = bestelrg.ART_CO_LEV AND bestel.Bestelnr=bestelrg.bestelnr AND bestel.Lev_code=offertes.lev_code)));
However, I need find an alternative to NOT IN if one exists and isn't too long? if anybody can offer an alternative it would be appreciated :)
same as the title goes, why i can't query those figures greater than 15% only? How am i suppose to query them right?.Here is my database you cant check query1 and query 2. I am using Ms Access 2010.
My company has satelite offices, which will be using database as well. For security and performance reasons, it is not feasible to have them connect to central server for a central back-end. (This may be different if we used a SQL backend, but migration takes time so that will need to wait).
My idea was I'd provide each satelite office their own local backend and have backend transmit the data to a central backend on a server which is fully replicated periodically while not in business. This would give the best balance between performance, security and network traffic.
But that creates the problem of possible conflicts between records' keys. I'm looking at GUID, and based on the amounts of records we usually do, I'm thinking GUID is a bit too excessive.
Maybe anyone can make suggestions if there's another way to prevent key conflict when the local backend and central backend synchronizes?
Hi, My database has 3 fields ID,Cust_Balance,Cust_Type. I need to assign Cust_Type as 1,2,or 3 based on Cust_Balance amount. If Cust_balance is lessthan 3 million then Cust_Type is 1,if between 3 and 10 million then type 2 and,more than 10 million as type 3.How do i accomplish this in Access ?Is IIf the right choice?:confused:
I need a natural unique id for current and new enrollees, and I do not want to store social security numbers. If a new person enrolls, I need a way to know if they're already in our system.
I was wondering if a lookup table would work, where you switch a random autonumber for a social. If this will work, is it more secure than simply storing the socials?
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
i have a columns as 1. contactname, 2. firstname 3. lastname 4. email and in this columns some emails are not matching with the contactname or some time firstname or some time lastname so i need the to find out the un matched contacts from the database.
I am looking to add a column in a query that will give a Y or No to previous column data if it contains TEXT or NUMBER (It could read "TEXT" or "NUMBER" or even Y for text or N for number).
I am trying to run a query where one field has a Not Equal parameter AND another field has Not Equal parameter (for example, Product Code Not Equal "A" AND Client State Not Equal "NY)
The result of the above query are all records that have Product Code = "A" are missing from the result. I want some of the "A" Product Codes; I just want to exclude the ones where the Client state is NY.
The list of values for each field is too big to state it in a positive way (I would have to list all the other 28 product codes and all the other 49 states).
What am I doing wrong? Thank you in advance for any suggestions.
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
And retrieving all **Basecode** if column data length >6 and with numbers '96', '78','54','15' by this query
Code: SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
How do i get other data which won't retrieve based on above queries, other than data mentioned on these queries like this
Code: SELECT tbl1.EID,tbl1.Description,tabl1.Basecode FROM tbl1 WHERE (IsNumeric(Left(Base,1)) AND Left(Base,1) Not In ("W","N")) AND NOT (((Len([Basecode]))>6)AND ((Left([Basecode],2))='15')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='54')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='78')) OR (((Len([Basecode]))>6) AND ((Left([Basecode],2))='96'));
I have 2 tables that i compere together with a query and if my criteria is OK than selected data is recordet in new table with some calculation....code:
INSERT INTO tblZaloga ( Proizvajalec, Tip, DN, Predvideno, Naroceno, Zaloga ) SELECT tblACAD_Urejeno.Proizvajalec, tblACAD_Urejeno.Tip, tblACAD_Urejeno.DN, tblACAD_Urejeno.KosovSkupaj, tblIN_Urejeno.KosovSkupaj, Sum(([tblIN_Urejeno].[KosovSkupaj]-[tblACAD_Urejeno].[KosovSkupaj])) AS Zaloga FROM tblACAD_Urejeno, tblIN_Urejeno WHERE (((tblACAD_Urejeno.Tip)=[tblIN_Urejeno].[tip]) AND ((tblACAD_Urejeno.DN)=[tblIN_Urejeno].[DN])) GROUP BY tblACAD_Urejeno.Proizvajalec, tblACAD_Urejeno.Tip, tblACAD_Urejeno.DN, tblACAD_Urejeno.KosovSkupaj, tblIN_Urejeno.KosovSkupaj;
Now i have a problem with racords that does not reach my criteria. The problem is that i dont know witch records are the one that does not reach my criterya? Can someone pls tell me how can i create a table like the code up there but with records that are not procesed?
Can someone pls tell me how can get not equal resoults from my two tables? I have table1 and table2 both with field "Layer" and what I want is to get only that data from table1 that does not have the same Layer as table2.Layer?
I try to JOIN table1 and table2 and i get the same resoult as with this code: WHERE ((tblACAD_MRK.Layer)=[tblLayerji].[NeteniLayer])
but thats exactly the oposite from what i want, so i try with this code: WHERE ((Not (tblACAD_MRK.Layer)=[tblLayerji].[NeteniLayer]))
and the code does not give me the resoult that i want, becouse i get all the layers for some reason :confused:
