Combine Into 1 Query

Nov 4, 2007

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

Thanks ....

View Replies


ADVERTISEMENT

Combine Query??

Oct 8, 2007

I run queries against an SQL database where I have read only access. This database has the following fields

EventNumber
BriefDescription
DetailDescription
EventCode
CauseCode
SignificanceCode
Resolution

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.

Any help is appreciated.

Jim

View 10 Replies View Related

Queries :: Combine Two Query Sql Code To Make Only One Query

Apr 5, 2013

First query = Sum Products:

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.

View 3 Replies View Related

Help To Combine Code In Query.

Jul 24, 2005

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:

View 2 Replies View Related

How To Combine Two Query Which Have The Same Column Name To Be Another

Aug 8, 2005

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.


can anyone help me??

View 8 Replies View Related

Query To Combine A Date And A Time

Apr 28, 2006

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.

What am I missing?

Thanks in advance,
Jake

View 4 Replies View Related

General :: How To Combine 3 Field Into 1 In Query

Nov 20, 2014

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

"Company name: IIf(IsNull([department]),IIf(IsNull([companyname]),[floor],[companyname]),IIf(IsNull([companyname]),[department],[companyname] & " " & [department]))"

View 4 Replies View Related

Queries :: Combine Cross Tab Query

Nov 9, 2013

how to combine 3 cross tab queries values into one daily progress report, transpose format. The values from each crosstab query are

1) Spent hours
2) Qty installed
3) Earned hours.

View 4 Replies View Related

SQL - Union Query To Combine 3 Queries

Jun 3, 2015

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]));

View 6 Replies View Related

Trying To Combine Multiple Queries Into 1 Main Query

Jul 10, 2006

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?

View 5 Replies View Related

Help W/ Query String- Combine Multiple Fields

Oct 3, 2007

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!

View 6 Replies View Related

Combine Monthly Product Sales Query

Feb 27, 2008

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.

View 7 Replies View Related

Queries :: Combine And Total Query Results

Aug 8, 2013

The results of my union query of 2 separate tables looks like:

Name - Count of Participations

Joe Blow - 7
Harry Smith - 11
Kate Upshaw - 8
Joe Blow - 3
Harry Smith - 5
Kate Upshaw - 13

I need to combine the duplicate names and total their participations to make a report look like:

Name - Count of Total Participations

Joe Blow - 10
Harry Smith - 16
Kate Upshaw - 21

View 3 Replies View Related

Queries :: Combine Four Identical Tables In One Query?

Apr 10, 2014

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.

Fields are:
UserID (PK)
AgentName
BadgeNumber (Employee Number)
Campaign (Department)
Coach (Supervisor)
HireDate
Status (Active / Leave of absense / Terminated)

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'll have to do this for each field involved...

View 9 Replies View Related

Reports :: How To Combine Queries Into A Summary Query Of Sorting

Mar 21, 2013

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?

View 1 Replies View Related

Queries :: Query To Combine Different Fields From Multiple Tables?

May 13, 2014

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.

View 7 Replies View Related

Can Merge / Combine 2 Or More Crosstab Queries Into ONE Single Query?

Aug 27, 2011

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.

How to do that? Is it possible? If so, how?

View 5 Replies View Related

Queries :: DLookup - Combine Multiple Criteria Syntax In Query

Apr 17, 2013

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.

View 3 Replies View Related

Modules & VBA :: Pulling Data Using Query - Combine Two Functions Into Third Function

Jun 28, 2013

I created two functions to use when I'm pulling data using a query (QBE). I would like to combine the two into a 3rd Function that works like this:

BB_Only = DishNetActivityCode = 1 and VideoActivityCode = 0
Hybrid = DishNetActivityCode = 1 and VideoActivityCode = 1
V_Only = DishNetActivityCode = 0 and VideoActivityCode = 0

Function DishNetActivityCode(service_code_string As String) As Integer
Dim CodeArray, i As Integer

[Code] .....

View 14 Replies View Related

Queries :: Combine 4 Tables Data That Falls Within Dates - SQL Query Returns No Records

Oct 2, 2013

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.

View 1 Replies View Related

Queries :: Combine Two Queries Result Of Second Query In Another Column

Mar 15, 2014

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"));

View 2 Replies View Related

Queries :: How To Do Combine Two Different Queries In UNION Query

Oct 28, 2013

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.

They Quays details:

1.Customer Credit Transaction1
2.Customer Credit Transaction2

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.

View 6 Replies View Related

Combine

Sep 27, 2007

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?

View 1 Replies View Related

Do I Have To Combine 2 Database Into 1?

Jul 18, 2006

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?

Any suggestion is welcome.

View 2 Replies View Related

Can I Combine Access And SQL?

Jun 19, 2007

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?

Thanks in advance!!
Jason

View 7 Replies View Related

Combine Records

Oct 26, 2005

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.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved