Queries :: Create Single Table Using Multiple Criteria

Aug 29, 2013

I am having trouble creating a query where I am trying to count number of records for different fields for a particular criteria, and combine the results into a single table.

My table is in the form,
TimeandDate,WS127m_Avg,WS82m_Avg....

I want to count those records where a 9999 is reported, and report by month. For a single field I can do this OK using,

SELECT DateSerial(Year([TimeandDate]),Month([TimeandDate]),1) AS [Month], Count(WS127m_Avg) AS 9999s
FROM CL_AllData
WHERE (((WS127m_Avg)=9999))
GROUP BY DateSerial(Year([TimeandDate]),Month([TimeandDate]),1);

I can't figure out how to report an additional field (WS82m_Avg) at the same time, checking for the same criteria in that field (i.e. WS82m_Avg = 9999).

View Replies


ADVERTISEMENT

Queries :: Multiple Combo Boxes In Single Query Criteria

Nov 20, 2013

I managed to get separate combo boxes to filter out results on a query, however now I have a slightly different problem.

I managed to get the combo boxes to filter records in different query criteria, i.e criteria A for field A, criteria B for field B, etc.

Now I would like to have several combo box filters in one criteria field, however no matter how much I try to move the code about, they either filter out nothing at all, or show up blank records as they're filtering one after the other, i.e filter for A, and then B, which clearly won't work as there will be no B if you've already filtered for A.

The code I'm using in each criteria box is;

Like "*" & [Forms]![MyForm]![Combo1] & "*"

and this works as a single criteria in a single field.

View 5 Replies View Related

Queries :: Add Multiple From One Table To A Single Record On Another Table

Aug 1, 2013

I have two tables

tbl_Orders
Cust OrderNo OrdLne Prod Qty
ABC SO123 1 AA 15
ABB SO124 1 AB 10

tbl_StockLoc
Prod Loc Qty
AA ZX12 10
AA ZZ12 10
AA ZZ13 50
AB CW12 15

One product can have up to 10 locations in the tbl_StockLoc

I need to print the following report (up to LOC10)

Cust OrderNo OrdLne Prod Qty Loc1 Qty1 Loc2 Qty2 Loc3 Qty3 ....
ABC SO123 1 AA 15 ZX12 10 ZZ12 10 ZZ13 50
ABB SO124 1 AB 10 CW12 15

View 1 Replies View Related

Dynamic Multiple Criteria In A Single Field

Apr 5, 2007

I have a query that displays all records. I need it to limit based on multiple criteria in a single field. I.E... instead of all employees from every section, I just want it to display employees from section A, C, D, F one time and next time maybe go with section B, C, D. For some reason the solution eludes my little brain.

View 3 Replies View Related

Combine Multiple Criteria In A Single Dialog Box

Sep 29, 2005

Hi,
I wanted to know if it is possible to combine multiple criteria in a single dialog box. Let me explain:

I have a query for which two fields need criteria entered by the user. In design view under criteria I have entered this type of expession Code:[enter criteria] for both my fields. Of course when I run the query I first get prompted for the first criteria and then once I click OK I get prompted for the second...

What I would like is to have a single dialog box with both criteria on 2 lines...

Your help would be mcuh appreciated

View 3 Replies View Related

Queries :: Create Table Query From Multiple Fields?

Aug 14, 2013

i essentially have 2 table:

1. Table BIC

A list of codes that will be updated monthly, which will be the basis for querying the second table. Approx 100 rows of data.

2. Table Original

A data file obtained from IT where i'll need to sort it to find any codes that are including in Table1. This includes approx ~ 10,000 row of data.

** note, the "BIC" from "Table BIC" can appear in any of the 5 BIC columns in Table Original.

What i need to do is create a query that will:

1. Search the "BIC" from "Table BIC" in all 5 columns of "Table Original".

2. Where it has a hit, it will create new table - for example, the first row of table Original includes the BIC "ABC" in the "BIC 1" column. A query would create table "ABC" and place this whole record (all 8 fields) in new table "ABC". No modification needed.

3. Where two (or more) BIC's from "Table BIC" appear in one record in "Table Original" - the result will only need to be placed in one of the new tables (really doesn't matter which one). For example, Record #4 includes the BIC "ABC" in field "BIC1" and the BIC "DEF" in the field "BIC4". Therefore, a new table would be created (either ABC or DEF) to capture this information.

View 4 Replies View Related

Create Database Consisting Of Table / Multiple Queries And A Form

Oct 28, 2011

I worked yesterday to create a database consisting of a table, multiple querries and a form. Today I went to open it and I got a different database I worked on months ago. The properties say it was created yesterday, but it is not the same database and I can not find the database I created yesterday.

View 1 Replies View Related

Queries :: Update Fields Of One Table With Multiple Criteria

Aug 12, 2015

How can i update fields of one table with update query where creteria are more than one 1

Example:

DoCmd.RunSQL "UPDATE Salary2009toUpdate SET " & Me.Combo2 & "='" & Me.Text4 & "'
Where Empid='" & Me.Combo0 & "'" and where bank='" & Bankname & "'"

What is the syntax error ...

View 5 Replies View Related

Queries :: Cartesian Table - Create Multiple Copies Of A Record To Use For Printing Labels

Aug 5, 2013

I am using a cartesian query to create multiple copies of a record to use for printing labels.

Here's my query that produces the cartesian result:

SELECT tblCount.CountID, tblDeliveryOrders.DeliveryOrderNum, CurrentCY.Deliveryorderlineitemnum, CurrentCY.Quant, CurrentCY.UOM1, tblContainerSizeCodes.SizeCode, tblContainerTypeCodes.TypeCode, CurrentCY.WasteDescription, tblEtidDodaac.EtidDodaac, CurrentCY.ETIDDocNum, CurrentCY.Pounds, tblEPAWasteCodes.[EPAWasteCodes(1B)]

[Code] ....

This works just fine in creating the desired result - EXCEPT I don't get all the records.

When I remove the 'cartesian table', and right join everything, then I get the correct results. If I keep everything as-is and reintroduce the cartesian table, then I get an error about there being an ambiguous outer join.

View 3 Replies View Related

Queries :: Allowing Users To Select Multiple Table Fields As Query Criteria

Apr 22, 2013

I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?

View 3 Replies View Related

General :: Create A Table With Single Text Field

Jul 8, 2014

Encountered this bug in Access 2010:

1) Create a table with a single text field
2) Enter some text values, including one which begins either "aa", "AA" or "Aa" (but not "aA"!)
3) Sort the field, and note where it puts the string(s) created in (2)

For me, they appear at the wrong end of the sorted list! I have tested this in Access 2007 and 2010 and it only happens in 2010. I'm using 32 bit Access on 64 bit Win 7 Pro.

View 4 Replies View Related

Queries :: Using Query To Place Single Grand Total On Row Based On Particular Criteria?

Dec 7, 2014

I have a very simple query on an accounts form to show a running transaction history.

Identifying from the TransactionID (shown for display purposes only - normally hidden) three or four postings make up one transaction.

Using TransactionID 10 as an example, I'd like to have a sum of total [Credit]-[Debit] and have the query display on one line (either at the top of £1,429, or at the bottom of £16,995) to identify this is in fact one transaction, having three posts.

Transaction 9 will have obviously have one total, as this is a single post.

11 the same as 10, by having one total Transaction Value either at the top of the row or bottom.

View 4 Replies View Related

Create Query With Multiple Criteria From A Many To Many Relationship

Sep 23, 2004

Alright I've been picking my brain for the past hour trying to figure this one out...

I have 3 tables...
1) Retailers-RetailerID, RetailerName, address etc. (500 entries)
2) Distributors- DistributorID, DistName (10 entries)
3) Uses- RetailerID, DistributorID

Sample data is as follows for 'Uses' table:
RetailerID DistributorID
1 1
1 3
2 1
2 6
3 4
3 5
4 8

I am trying to create a search form based on a query which will return retailer information for those retailer who use multiple distributors.

For Example...There will be 2 text boxes and a user can enter a distributorID in these text boxes. If only 1 distributorID is entered, it will display records for that one.

I have tried the following but I have been unsuccessful:

SELECT Uses.RetailerID, Retailers.Address, Count(Uses.RetailerID) AS CountOfDistributorID
FROM Retailers INNER JOIN (Distributor INNER JOIN Uses ON Distributor.DistributorID = Uses.DistributorID) ON Retailers.RetailerID = Uses.RetailerID
WHERE Uses.DistributorID = 1
GROUP BY Uses.RetailerID, Retailers.Address
HAVING Count(Uses.RetailerID) > 1


Is this possible to accomplish with the way I designed this database? If so, can I get a little advice? I know this is a long question that can't be answered in full but I would just need to basics in order to be on my way.
Thanks in advance!

View 1 Replies View Related

Create A Query That Has Multiple Criteria Selected From A Form?

Nov 26, 2013

I need to create a query that has multiple criteria selected from a form For example. I will always have the customer name but then the user might also choose any one or more of the following, start date, end date, number etc

I have tried to create a select query and added the customer form element to the criteria but I am not sure how to do the rest because it will always have at least one more criteria but I am not sure which one and it may have up to 5 more criteria.

View 5 Replies View Related

Consolidating Multiple Queries Into One Single Result

Dec 17, 2006

I couldn't find a similar question already answered, so here it goes. I have multiple tables used to store information on different research materials:

table Books:
- BookID
- Author
- Title
- Year
- Cabinet

table Papers:
- PaperId
- Author
- Title
- Year
- Cabinet

table Catalog:
- CatalogID
- Author
- Title
- Year
- Cabinet

And I have a query for each of those tables that helps me find information. What I'm trying to do is to consolidate the result of those individual queries into one single query that later I can insert into a form to display a search result.

So the resulting query would look like:

Type.....ID....Author.....Title....Year....Cabinet
Book.....001...John.....Good book....2005....C01
Book....002.....Mary....Other book...2006...C01
Paper....001....Albert...PaperABC....1987....C01
Paper...002....John.....PaperXYZ.....2006....C02
Catalog...001...Mark....Cat00A....1989.....C02
Catalog...002...Bill......Cat00B.....2004.....C03
etc.

Is this possible?

Any help is appreciated!

Thanks.
Daniel

View 4 Replies View Related

Single Set Of Parameters For Multiple Queries In A Report?

Oct 21, 2004

I'm pretty new to Access, so if I'm doing this the hard way, that's why.

I work in a quality control position, and I'm trying to set up a single Access database for the QC staff to use instead of everyone having their own seperate Excel workbooks.

The issue I'm running into at the moment is on a report. Each record is graded on four seperate types of criteria, Error Type 1, Error Type 2, Error Type 3 and Error Type 4, all of which need to be reported on seperately. So I have at least four queries set up, all with the same parameters (right now, just review date.) I'm trying to pull through all four queries on the same report, and so far I've had success having the report ask for the parameters only once and then applying it to all four queries. However, I'm running into a problem where Access is now forcing the filters of each individual query on to each of the other three queries, so it's only pulling through records for all four queries that match the criteria of all four. Any record which only matches the criteria of one, two or three of the queries is being left off the report.

Sorry if this sounds confusing. Anyone have an idea as to what I'm doing wrong?

View 3 Replies View Related

Form For User To Create Custon Reports From Multiple Criteria

Jan 4, 2006

I am trying to put together an form to be used in an academic administration database.
Ideally, I would like to create a form that will allow a user to select criteria and get a list of all students fitting that criteria.
For example:
Show me all a) all Economics majors with b) 30-50 Credits who c) have not taken English 101.
All of this data could be compiled into one query, but I would like to create a form that will allow a user to select what data he or she would like to see.
(Maybe they only want to see graduates)
I hope this makes sense.
And I hope someone can help point me in the right direction.

Thank you!

View 2 Replies View Related

Forms :: Assigning Multiple Entries In One Table To A Single Entry In Another Table

Jul 24, 2013

I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.

The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.

Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).

How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?

View 10 Replies View Related

Modules & VBA :: Generate Multiple Records In One Table From Single Record In Another Table

Sep 20, 2014

I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.

I have one table with the following field and data:

ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5

I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:

ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green

The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.

View 8 Replies View Related

Queries :: Import Multiple CVS From Single Folder Into Access

Jun 2, 2013

I have some patient data which outputs from another system generates 2 CSV files, for example (John Doe Ankle) and (John Doe Calf). Where Angle and Calf are the part of the body and John Doe the patient name.

Here is the exact output side by side of both of these files: [URL] ....

The CSV file generated contains the field names in column A and I have to pull both of these into a single table OR maybe it should be two tables in Access and then join them (only problem is that there isnt any unique keys in the raw data to tie them together).

Ideally, Id love to have a Macro on a form in Access that runs this process into the table, then allows this data to be merged to a single report containing all of this data.

View 1 Replies View Related

Queries :: Date - Multiple Columns Into Single Column

Jun 6, 2013

I want to create the date from multiple columns to single colums. Just for example

table 1 (local purchase)
Itemname Date
Apple 12/01/2013
Mango 13/01/2013

Table 2 (Import purchase)
Item Name DAte
Apple 12/04/2013
Mango 08/06/2013

Now i want to make one query, which can you the date as follows when we give criteria = apple

Item Name Date purchase mode
Apple 12/01/2013 local
Apple 12/04/2013 Import

Means two dates from different table into one query column... One way in my mind to make one table for both tables.

View 9 Replies View Related

Multiple Forms/single Table?

Dec 4, 2006

Hello friends,I have a pretty noobish question. I was wondering if it was possible to have several (3 in my case) different forms that apply data to a single table? The main table has three different types of data: 1. Client info 2. Services A 3.Services B. Each type of data has about 7/50/12 different fields of data in each set so that trying to include all this data on a single form is not very user friendly due to clutter.I have three forms that input data into the table, however the record navigation command buttons that I have on the forms do not apply the data to the correct record. For instance, when I input the Client info for client #2, and click the "open services A form" command button, the form opens on client #1 Services A instead of to a new record. Is this something I have done wrong, or is it due more to MS Access limitations? Thanks

View 3 Replies View Related

Queries :: Return Multiple Rows From A Single Record In Database

Jul 16, 2014

For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows

1st Obs Start
1st Obs End
2nd Obs Start
2nd Obs End
3rd Obs Start
3rd Obs End.

I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:

ID......Obs Start.............Obs End........
1....[1st Obs Start].....[1st Obs End]....
1....[2nd Obs Start]....[2nd Obs End]....
2....[1st Obs Start].....[1st Obs End]....
2....[2nd Obs Start]....[2nd Obs End]....
2....[3rd Obs Start].....[3rd Obs End]....
3....[1st Obs Start].....[1st Obs End]....
4....[1st Obs Start].....[1st Obs End]....

etc.

I could then quickly count how many obs periods started within the desired month.

View 14 Replies View Related

Queries :: Run Multiple Expressions On The Same Data Field In A Single Query?

May 11, 2014

I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.

Please see attached jpg for example.

SQL: SELECT Count(StockData.status) AS FaultyPCs
FROM StockData
HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));

What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.

But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.

Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.

View 7 Replies View Related

Queries :: Counting Multiple Dates In A Single Query Field

Feb 8, 2015

I'm creating a database for my wife to use in her work, one of the fields is dates visited and the user should enter "00/00/0000, 11/11/1111, 22/22/2222, 33/33/3333" in this format. The field is a large text format because there could be anything from 1 date to hundreds. What I need to be able to do is in my query I need to enter 2 dates and have access return a how many dates exist between those 2 date values. eg It will pop up a box asking for start date and then another asking for end date and then it has to return the count total of how many dates exist between those two date values. I don't know VB and have had only basic training in Java and C# none of which involved Access. The only way I can think of doing it is to have access count the number of commas within the date field required but I don't know the formula required assuming it can even be done.

View 1 Replies View Related

Export Multiple Queries Into A Single Spreadsheet In Different Range Of Cells?

Jun 10, 2012

I need to export multiple queries into a single spreadsheet in different range of cells. Means one query need to be exported from B2:E2 and second query need to be exported from B10:E10. In this way need to export 18 queries' result into one sheet only on different name range.

I am using Access 2007 and need to export data into Excel 2003 format.

View 4 Replies View Related







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