Can I Make These 2 Queries Become 1?

Aug 10, 2005

I have this query:

SELECT testScreen, Count(ID) AS testCount FROM SERPTestInput GROUP BY testScreen

It returns a count for each testScreen that appears in the table.

Then this query returns the same thing except it joins another table and adds the criteria that the status for the record must be successful.

SELECT testScreen, Count(testID) AS myCount FROM Results RIGHT JOIN SERPTestInput ON (SERPTestInput.ID=Results.testID AND Results.testStatus = 'Successful') GROUP BY testScreen

So both queries currently generate an 8 row table with the first column being the testScreen and the second being a count. I would like to somehow combine the two so I get a three column, 8 riow table. Column 1 would still be the testScreen, column 2 the total count for each testScreen and column 3 would be the successful count for each test screen.

In short, can the above two queries be combined into one?

View Replies


ADVERTISEMENT

How To Make Dynamic Queries

Jun 19, 2006

I have several fields that have a drop down list. Say the Field is called "Age" and I have choices in the drop down menu as 1, 2, 3, and 4. How do I create a query that would accept input from the user to show 2 of the 4 or 1 of the 4 or all of the choices using one query that has checkboxes, and possibly using a form to do so. I have a form that has a list of all entries. Now I just need to make it able to filter based on user input.

Say I have checkboxes for 1, 2, 3, 4, and all choices. Can a list filter only what I check? Can I create a form that would have all the choices on it and just click a button that says "submit" and have it filter, or print the form. How do you get the checkboxes to filter when checked, but not when unchecked? Where does Access store the true/false, on/off, yes/no responces that come from the checkbox? Is there a variable that gets set to a 1 or a 0 based on the status of the box?

View 1 Replies View Related

Never Use ‘make Table’ Queries.

Mar 14, 2007

I know that this could open a can of worms but is the above statement true. I have always avoided using delet and append due to database bloat. I know its more difficult to set the field types in a make table query but it means that the database doesn’t need to be compacted nearly as much as delet and append.

If my understanding is incorrect please explain top me why delete and append are better.

Thanks

View 9 Replies View Related

Make-table Queries For Web?

Sep 25, 2006

Hello All, I'm using MS Access 2003 for a web DB... Am wondering.. is it bad to use Make-Table queries for the web..? Or should I just do all the SQL joining and linking stuff just with more SQL code?

Just curious about the impact Make-Table queries have on the Web.. Slower? Not a good idea?

Thanks for any input you can give
John

View 1 Replies View Related

Row Numbering In Make Table Queries

Jul 28, 2006

I have a linked table with 3 significant columns in it:
Marque
Model
Volumes
(there's actually about 12, but I only need these)


and I need to create a new table summarising the contents and creating new columns at the same time
Marque
Model
Model_name (concatenate marque and model, easy to do)
Vols sorted: Descending
Rank (this is the problem)

I need to 'Rank' the table so the model with the highest volumes is ranked #1 the second highest is #2 and so on.
Is there a command within access to allow this to happen? in SQL-Plus from Oracle I can use the Rownum command to create the entry, but this does not have an equivilent in access.
At present, I'm creating the table without the rank field, then adding it in design view, setting it for autonumber, saving, and resetting it to number. This is long-winded and frankly, messy and wrong. I shuld be able to do this in one go, but I can't. I've been using access for about 6 years solid and have not been able to resolve this 3-month-old problem and it's driving me mad.

Thanks in advance for your help.

View 3 Replies View Related

Avoiding Make-Table Queries

Mar 28, 2006

I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?

View 3 Replies View Related

Avoiding Make-Table Queries

Mar 28, 2006

I have 4 queries needed to power one report. I've tried combining the queries into one so I can run the report off that. However, there is too much data and I get all sorts of errors. My only solution was to toss that data into a table thereby eliminating all the calculations and expressions and cleaning up the data. Is there a more elegant solution to this issue?

View 2 Replies View Related

Queries :: How To Make Delivery Note

Aug 14, 2014

when i want to make a delivery note - on many cases the Delivery Note Number that shows has more than one house name, Plot Number and Product - at the moment it only shows one specific house name, plot number and product when i would like it to show every one of these under the delivery note number and delivery note date?

View 2 Replies View Related

Queries :: How To Make Subdatasheet To Look Like Default One

Jul 2, 2015

Linked field is shown both in Master and child tables, but not in the default subdatasheets made by when a relationship is made between a Master and Child Table.

View 5 Replies View Related

Queries :: Make A Union Query

Dec 18, 2014

I have two basic lists of employee names, ID's, phone numbers etc, with one field containing an attachment with a picture of their ID. I don't want to permanently combine these two lists of employees, as they work in different departments, but for certain events I need to be able to print a report with a list of all their names, ID numbers, and corresponding ID photos.

I tried to make a UNION query, but can't do it with the attachment field. If I leave that field out, it's no problem. To simplify, I have been doing a test run as follows, with just the name field and photo field (field 1).

SELECT [Starting Gate employees].[Employee name], [Starting Gate employees].Field2
FROM [Starting Gate employees]
UNION
SELECT [Farrier employees].[Employee name], [Farrier employees].Field2
FROM [Farrier employees];

View 2 Replies View Related

Queries :: How To Make Whole Statement To Work

Jun 14, 2013

I am trying to get an IIf statement to work, but I am not quite sure how I can lay it out properly so that it works.So this is what I need

(IIF(([PAON_TEXT]= "Abbey Parade" AND [SAON_TEXT] AND [SAON_START_NUM]), (THATS THE CONDITION)
[SAON_START_NUM] & " ", (THATS THE TRUE PART)
(IIf(Not IsNull([SAON_TEXT]),[SAON_TEXT] & " ","") & (IIf(Not IsNull([SAON_START_NUM]),[SAON_START_NUM],"") & IIf(Not IsNull([SAON_START_SUFFIX]),[SAON_START_SUFFIX] & " ") & IIf(Not IsNull([SAON_END_NUM])," - " & [SAON_END_NUM] & " ","") & IIf(Not IsNull([SAON_END_SUFFIX]),[SAON_END_SUFFIX] & " ","") & IIF(([PAON_TEXT]= "Abbey Parade" AND [SAON_TEXT] AND [SAON_START_NUM])," ","") & iif(NOT IsNull([PAON_START_NUM])," " & [PAON_TEXT],"")
(ALL OF THIS THE FALSE PART)

I know the syntax for the FALSE PART is wrong how can I make the whole statement to work?

View 5 Replies View Related

Queries :: Maintain Primary Key In Make Table?

Apr 9, 2013

Is there a way of keeping/setting the primary key when using a make table query?

View 2 Replies View Related

Queries :: Make Table Query In Different File

Aug 30, 2013

I need to put together a make table query, already got all the fields sorted the way i want the problem is that query is located in my databases Front End and i need the table to be made in the Back End (giggity), the filepath for the back end is actually stored in a "Master Control" table if this makes the process easier...

View 3 Replies View Related

Tables :: Make Table Queries - Indexing?

Jan 2, 2014

I have a complex series of calculations to perform, and I have found the best way to achieve my end is to create new tables by the Make Table queries.

The trouble is I have many tables to create and the downstream tables refer to the previously created tables; I have recently discovered that queries perform much quicker if they are indexed (duplicates OK). I've looked at my tables created by the MakeTable queries, and they all seem not to be indexed.

How do I make them indexed automatically?

View 2 Replies View Related

Queries :: Make Table Query On Like Fields

Sep 18, 2013

I am developing a Make Table Query from 2 tables, one of which has an "Employee Name" field (lastname,firstname) and the other table has separate fields for LastName and FirstName. I've been able to accomplish almost what I need by:

WHERE ((([Table 1].[EMPLOYEE NAME]) Like [Table 2].[LAST NAME] & "*"));

Which works fine except when there are 2 employees having the same last name, then it generates duplicates. I suspect there must be a way to do this by incorporating the first name field in the sql statement but that's beyond my ability. I realize that names are not good things to base a query on, but the 2 existing tables have been preset and populated by others and I don't really have the capability to change them.

View 3 Replies View Related

Queries :: Make Table Query To Linked BE

Jul 15, 2014

I have a make table query which creates the new table on the local front end.I have now split the DB, so need to know how I can get this query to make the table on the BE i.e. how do i link it.

View 14 Replies View Related

Queries :: How To Make Cascade Update / Delete

Jan 13, 2015

What I'm trying to do is making Cascade update and delete between two tables. For example in my case I have Field called Full name in table1 and I want the same field in table2 that will update when something is added in table1 and vice versa. I have tried relationship but I got an error that those fields does not have any unique value.

View 3 Replies View Related

Queries :: How To Make Total Columns For ONE Field

Jul 2, 2015

I am trying to create a query that ONLY shows me the total count of each combo box choice for one field. It sounds very easy and I'm sure it is but everything I try tells me I am using the wrong syntax, or requires me to have rows when I only want column totals.

I have a combo box with a dropdown of 10 common places that people travel to. I want the report to ONLY show the total number of people who chose place 1, place 2, place 3... etc. But I always have to do according to date or time or something. I want it to look like this

Place 1 Place 2 Place 3 Place 4
13............36.........3........77

So essentially there would only be one row. But access will not let me. Is there something I'm doing wrong? Or is this not possible?

View 8 Replies View Related

Queries :: Make All Of Values Divide Correctly?

Jul 31, 2014

Simple division, I thought! I'm dividing a Sum of hours by Pay Periods to determine the average of missing hours for year-to-date. Most of the division works perfectly, and some doesn't and appears to be related to values of 1 or less than one.The formula: [Sum]/[Pay Periods].Sum relates to total missing hours and pay periods are total pay periods for the year so far.What comes out wrong:

1 (hr) / 12 (pay periods) = 8.33, when it should be .0833
0.5 / 12 = 4.166 when it should be .0416

What is right is everything else, starting with 1.5 hrs

1.5 / 12 = 0.125

What do I need to do to make all of the values divide correctly?

View 3 Replies View Related

Queries :: Validating A Query To Make A Report

Feb 20, 2014

I am making a football database for my sixth form project. It consists of 6 teams and i need to make a top scorers report for each team. I am attempting to make a query for each report which will consist of the fields needed, under the field 'Team Name' i need to validate it for each team so that each query only shows their specific team, then i can make it into a report. But i do NOT know how to validate it. In human words i want it to say something like this " Only include this player if value of the team name if it is equal to 'St.Albans city east' (one of my teams)".

View 1 Replies View Related

Queries :: How To Make Hierarchical Structure For Table

Jun 18, 2013

I have a table with below fields and data

Code:

DocNo TransmittalNo TransmittaltoCon

Code:
Doc-0001 tt-0002 con-0005
Doc-0005 tt-0002 con-0003
Doc-0001 tt-0002 con-0007

Now I like to make a treeview with 3 level of information of my table that level 1 is (Doc No) level 2 is (Transmittal No) and level 3 is (TransmittaltoCon) , after searching in google I read I have to make another table (query) with parent and position field for example

Code:

ID PARENT POSITION OPTION

Code:
1 0 0 DOC-0001
2 1 0 TT-0002
3 0 1 DOC-0005
4 1 1 CON-0007
5 3 0 TT-0002
6 5 0 CON-0003

View 1 Replies View Related

Queries :: Make DSum For Products Coming In And Out?

Nov 9, 2014

I am trying to make a dsum for products coming in and coming out. find the example below

Products In
Apple 10
Mango 5

Products out
Apple 5

Now I want to show statement for sum as follows

Product. In. Out. Net in hand
Apple. 10. 5. 5
Mango. 5. 0. 5

But it is showing only

Product. In. Out. Net in hand
Apple. 10. 5. 5

what I understand is dsum cannot find the out field for mango as there is no field found for mango

View 14 Replies View Related

Queries :: How To Make Certain Range Of Dates For Query To Run

Oct 9, 2013

I have a query and I want to make a certain range of dates for the query to run. For example: from 10/6 to 10/7. What would be a parameter for this range of date? Should I put it on a query?

View 11 Replies View Related

Make Table Queries And Concatenated Memo Fields

Jul 13, 2006

I have a Make Table query that includes a new field concatenated from a numeric field and a memo field, with some assorted text elements -
Issue Description:"("&[number]&") "&[Description]

When I run the Make Table, the new field "Issue Description" is created as a text field, NOT as a memo field.

Is there any way to insure that this field is created as a memo field.

Thanks.

Susan

View 1 Replies View Related

Make A Querie That Searches For Other Queries That Contain A Certian Word

May 10, 2007

ok, i wanna make a query that will display the information from other queries containing the word "complete" in their title. All of these queries have the same columns names to them so they should fit together nicely but if someone adds a new query in the future with the word "complete" in the title i want this query to pick it up. Is this possible?

View 9 Replies View Related

Queries :: Make All Data Disappear When In Datasheet View

Nov 22, 2013

I have a query that's been working fine for years, however...the powers that be have requested a change. We need to track the lining owners in cars. We've recently gone to SQL on the back end (which I basically know NOTHING about) but I set up the new table, refreshed the connection, etc.

I can see the table fine and it says it has a primary key, auto numbers, etc. however, as you can see from the jpg below when I add the table to the query and link the id as in all the other tables, for some reason, this one makes all the data disappear when in datasheet view.If I delete the table from the query - it all comes back.

View 5 Replies View Related







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