When users enter one of the 3 "Codes", they can enter as many as are appropriate. So an event may have 3 EventCodes, 2 CauseCodes and 2 SignificanceCodes for example.
When I run my query, I get a different record for each. So for the above scenario, I may get 12 records. Problem is I only want one record for this event. Using a query, how can I combine all the EventCodes together and the CauseCodes together and the SignificanceCodes together...maybe separated by a space or comma? If I have to copy the data down locally, that's okay. I am wondering if an Update query could be used somehow, but I am not sure how to do this.
I'd appreciate any ideas on how to combine the following code into 1 query, without "If ... Then ... Else": If Now() >= TransitionStartDate And Now() <= TransitionEndDate Then DoCmd.RunSQL "SELECT [Contractors Trained Query].LastName, [Contractors Trained Query].FirstName, [Contractors Trained Query].CompanyName FROM [Contractors Trained Query] WHERE [Employees Trained List].[This Years Training Date] Is Not Null OR [Employees Trained List].[Last Years Training Date] Is Not Null ORDER BY [Contractors Trained Query].LastName;” Else DoCmd.RunSQL "SELECT [Contractors Trained Query].LastName, [Contractors Trained Query].FirstName, [Contractors Trained Query].CompanyName FROM [Contractors Trained Query] WHERE [Employees Trained List].[This Years Training Date] Is Not Null ORDER BY [Contractors Trained Query].LastName;” End If
Code: SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;
Second query :
Code: SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM FROM [Sum Products] GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));
I need to combine those two query sql code to make only one query.
I am using the two lines of code below Each in separate columns of a query (or two text Boxes on a form) to calculate the elapsed time between two dates formatted as General Dates. The first line of code (in column A, "txtTime1") calculates to the total minutes between the two dates, the second line of code (in column B, "txtTime2") takes the minutes and converts them to total elapsed hours and minutes. They work great, however, I would like to combine this code and place it in only one query column or form Text Box. My VBA is not up to it, can someone out there show me how to combine the code below to produce the hours and minutes in one line of code? Thanks
=DateDiff("n",[StartDate],[CompleteDate])
=[txtTime1]60 & Format([txtTime1] Mod 60,":00") :confused:
Hi friend I need your help. I have made 2 query (named "QryReceivesum" & "QryWithdrawsum"). But query have the same common column called "ProductId". I want to combine 2 query to be just one.
Currently I have mae one query named "QryStock". but the data from this qry is not correct.
As you can see, it doesn't show the number of product from productID3.
Hi, I am trying to create a general date field by combinging a date (short date) and time (medium time) field. The fields are named recDate and recTime for the two I am combining, and cmbDateTime for the result field.
When I run the query, the result I receive is an errror stating that it is a data type mismatch.
how to combine 3 field into 1. For instance. I have a customer table with company name, branch, floor, department. then i am gonna create a query called customerextended and combine all these 3 field so that I can select them in the combobox.
I have a sample here but I just dont know how to edit it...
I have three queries that make a training list based on a person's role, team, and ad-hoc exceptions. The personnelID field is filtered by a listbox on a form. Each of these work great on their own (nice!). Now I want to combine them. I made the below union query that works... however when it is run, I am prompted to enter the personnelID again. This prompt only happens once. Do I need to incorporate a qhere statement somewhere, even though each of the individual queries have them already?
SELECT First_Name, Last_Name, TrainingTitle, PersonnelID FROM qryPersonnelTrainingByRole UNION ALL SELECT First_Name, Last_Name, TrainingTitle, PersonnelID FROM qryPersonnelTrainingByTeam UNION ALL SELECT First_Name, Last_Name, TrainingTitle, PersonnelID FROM qryPersonnelTrainingByAdHocPersonnel;
The where statement of potential use:
Code: WHERE (((tblPersonnel.PersonnelID)=[Forms]![frmMain]![lbxPersonnel]));
Can anyone help me with how I can accomplish this?
Here is what I currently have:
10 SQL Pass-through queries to update different Date field (Date1, Date2, Date3, etc.) based on the value of the next date field.
This is how each individual query is set up (there are 10 in total) UPDATE tblApplications SET tblApplications.LastContact = [date1] WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
Here is what I would like to have: (as 1 query)
UPDATE tblApplications SET tblApplications.LastContact = [date1] WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
UPDATE tblApplications SET tblApplications.LastContact = [date2] WHERE (((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
UPDATE tblApplications SET tblApplications.LastContact = [date3] WHERE (((tblApplications.[Date Completed]) Is Null) AND ((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Not Null) AND ((tblApplications.Date4) Is Null));
Can I somehow combine them together like this or do I have to keep them as 10 seperate queries?
Hey guys- I have 2 tables- and need to find the record matches between them via their product code. Table 1 has the code as follows (and is considered 'the Master List')- xxx-xx-xxx Where x is an integer.
Table 2 has split the 3 sections of the code out to 3 individual fields, like this- Field1 Field2 Field3 xxx xx xxx
I need a query that will combine the 3 fields in table 2 to the same format as Table1 so that I can find the matches between them. I know this is a basic question, but I'm a learning newbie. How would I write that in a query? I don't think I want to write the format to the table- just include it in the query somehow... Thanks!
I was wondering if there is a way to combine sales by month for a year where it would show the product then for say January and the total sales and so on for each month.
I have multiple databases (one for each department). All 4 databases have an Agents table (tblAgents). Now I am creating another database that uses all of the agents on site. What I want to do is link all the agents tables and combine the values in one query.
The tables are linked to my new database. So logically, I'd think my next step is to query the tables. This is where I'm stuck. How do I say:
Agent = tblAgentsDepartment1.AgentName and also tblAgentsDepartment2.AgentName and also tblAgentsDepartment3.AgentName and also tblAgentsDepartment4.AgentName?
i intially, I wish to have attendance stats for 3 groups of people over 2 days in a report
i have the two queries for the raw data, but wish to combine into a report that will ultimately become a "dashboard" report for half a dozen queries., and can only get one or the other to work do i need to combine the queries into a summary query of sorts?
I get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.
Because I have more than 3 queries, it is really hard for me to manage. Therefore, I was thinking of having 3 crosstab queries to show in one single query. This way it will save my time from moving back and forth.
I am trying to create a Dlookup in Access 2010 within a query using query wizard. I want to lookup the tax rate for an employee based on a salary range and their 'tax category' (string). Through troubleshooting I can get the criteria to work separately.
These are: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'")
DLookUp("Base","TABWT",[grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")
These work and return the correct values for each column/row when I run the query.
However, when I combine the criteria (using the build wizard) as follows:
Expr1: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'" and [grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")
The Dlookup will returns 0 values but will not give an error message.
I've tried quite a few variations on syntax and quotes and so on. However, it's just not working for me.
I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:
Code: SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC FROM [1733_All Print Orders] WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013# GROUP BY [1733_All Print Orders];[Application]
[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.
I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records
To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.
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"));
Am trying many times in UNION query but its not working because there is different field names and only some fields are matched. So I need to both query's are combine in 1 query.
Above both queries are already combined in UNION query as (Customer Credit Transaction Final) its not a problem.So now I need to do combine the above Union query Customer Credit Transaction Final & ReceiptformQry.
The both query details:
Customer Credit Transaction Final SELECT BillBook1.TID, BillBook1.BILLNo, BillBook1.BILLDate, BillBook1.Customer, BillBook1.BillMode, [BillBook1 Vat Details].[TOTAL Rs] AS [CC Amount] FROM BillBook1 INNER JOIN [BillBook1 Vat Details] ON BillBook1.TID = [BillBook1 Vat Details].TID WHERE (((BillBook1.BillMode)="Credit")); UNION ALL
[code]...
Above fields are need to merge in Union query or otherwise. and remaining fields are needed to show separately.
I have a question that I don't know how to really explain, so here is an example of the table I have:
35510157.32 355102267.44 35510372
The first column (35) is Employee ID. The second column (5101) represents a certain time code. And the Last Column (57.32) represents an amount of time.
I am trying to create a query that puts the data like this:
3557.32267.4472
There is about 3500 different Employee ID's and each ID can have up to 10 different time codes. Is there a way of doing this without doing a Make Table or Update query?
My application links to 3 mdb (eg. 1a.mdb, 2b.mdb, 3c.mdb) backend database.
It works as the following: if an order is placed by a company (public sale), the order will be stored in 1a.mdb, if the order is for private sale, the order will be stored in 2b.mdb. The items for public sale are stored in 1a.mdb, the items for private sale are stored in 2b.mdb. It means an order cannot combine both public and private sales, but in fact this always an issue. 3c.mdb is used for storing common data, such as customer details.
Now I would like to create an order that includes both public and private sales. How I can do that? Do I have to combine 1a.mdb and 2b.mdb into 1 database?
I was wondering how to go about combining both Access and SQL together. Am I going to write the SQL codes in VB and then Access will know how to combine the tables I've created with the SQL codes?
I need to know how to combine two records. What I have is a text file that is imported into a table. The problem is, the text file has 4 fields on one line then 3 fields on the next line. Is there a way to combine these two lines into one record? I do not want to do this in the text file. I want to import the file and run some code to combine the two records into one record, delete the second line, and go to the next two records. What can I do? Sorry for any spelling but I have to run. Thanks for any help.
