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 Replies


ADVERTISEMENT

Tables :: Consolidating Multiple Tables In One Single Table (Sorted Ascending By Time)

May 30, 2014

What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.

I have tried Union coding but always get Syntax Error etc.

View 8 Replies View Related

Queries :: Two Tables To Result In Single Column With Field Name Identifier For Each Record

Apr 27, 2015

How to get this one to display in a single column.

I know how to do this wiht VBA. But, this output will need to reside on a SQL Server View. So I need a SQL language solution. If it can work in MS Access Query, it won't be too difficult to test then translate to SQL Server.

Customer Table with PK Customer_ID.

There are two tables with FK Customer_ID.

1. Table Lease1 - Has 3 Fields - the form code enforces No Fields -or All Fields. The red * indicate a Required field - These 3 are entered together.

2. Table Lease2 - Has 1 field with 0 to Many records.

Goal:

The Type shows up in a single column.

Each Type shows where the data comes from (Lease Type, Surface Owner, Mineral Owner, or Hz Lease Type)

Challenge:

Lease1 table has 3 fields that need to be transformed into a single column.

Lease2 table has 1 field to be appended to the single table.

Then, there is the column that identifies where the data came from based on the column name.

View 6 Replies View Related

Consolidating Fields From Multiple Records Into One Record

Nov 29, 2012

In Access 2010, I have a table with two fields. The first field (Investor Name) contains several occurrences of the same text (i.e. Investor A occurs 2 times, Investor B occurs 3 times, etc.). The second field (Investment) may or may not contain duplicates (i.e. more than one Investor may have invested in the same Investment). I'm trying to create a query that will show me, in one record, all the Investments that each Investor has made.

So for the following data...

Investor Name
Investment

Investor A
Investment 1

Investor A
Investment 2

Investor B
Investment 1

Investor B
Investment 3

Investor B
Investment 4

I want the query to report back the following...

Investor Name
1st Investment
2nd Investment
3rd Investment

Investor A
Investment 1
Investment 2

Investor B
Investment 1
Investment 3
Investment 4

To clarify further, I'm NOT looking to concatenate the results into one field. Instead, I want each Investment to appear in its own field, so that I can pull each one into a report later. Ideally, there should be no limit to the number of Investments that each Investor has made, although practically speaking, it likely won't exceed 10 or so.

View 14 Replies View Related

Queries :: Consolidating Records In From In Same Table

Jan 31, 2014

I have a table with the following

Code:
fname,lname,location1,location2
-------------------------------
john, doe, dublin, null
john, doe, null, galway

and i wish to consolidate it to show like

Code:
fname,lname,location1,location2
-------------------------------
john, doe, dublin, galway.

I could do it by creating a new table, then merging, but its cumbersome.

View 6 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

Queries :: Calculate Multiple Fields For One Result - Callsign

Feb 21, 2015

I have never created a single query that is this complicated.

I have a time card database that I created back in 2003 and over the years more and more new task have been added to track volunteer hours has been incorporated into it.

The current task at hand is to determine if each member has achieved or meet certain requirements each year.
They are attend 8 or more meeting and check into 5 or more nets and be Net Control Operator for at least 1 radio net and be involved with 1 or more Public Service events. Sounds simple right?

I have a table that contains all the information that is need to get the result I want but am having a problem getting that result with a single SQL query. Everyone you talk to says use SQL query to obtain calculated vales and never store/save them.

So from the work table below meetings consist of 4,5,6,8,9 and nets consist of 10,11,14,15 and events consist of 2, 13 and the NCOflag is T/F.

So those are all the parts, count the number of meetings, nets, events and NCO and set the meets requirement flag T/F
I keep getting "You tried to execute a query that does not include the in specified expression <name> as part of an aggregate function. (Error 3122)"

In this case it keeps complaining Callsign.

You did not enter an aggregate function in the TRANSFORM statement.

Here's the statement I created:

SELECT [Work Hours].Callsign, [Work Hours].[Work Code], [Work Hours].NCOflag,[Work Hours].SubMtg, [Work Hours].SubNet, [Work Hours].SubNCO, [Work Hours].SubEvnt, [Work Hours].ReqSubTotal
FROM [Work Hours]
WHERE (((Year([Date Worked]))=(Year(Date()))))

[Code] .....

It is failing on the first HAVING (([Work Hours].SubMtg) =Sum(). I have tried many variations and changes but don't see what is wrong with it.

View 2 Replies View Related

Queries :: Search With Multiple Fields Result From Query

Dec 26, 2013

I want to make a form which will allow me to get data from a query and search using 3 fields parallel.

Attached the database in 2010 format.

Password of the db is "nolimit".

The query PTM & Equipment should be bound with the form and

If I enter month, PTM and equipment need rest 2 fields displayed.

Like choosing the above 3 fields from the drop down , the next 2 text box should display the rest two fields.

View 3 Replies View Related

Queries :: Display Multiple Rows Into One Row Query Result

Mar 25, 2013

I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.

Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.

I am currently using Access 2010.

View 5 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

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 2 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 :: 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 :: 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

Counting Results Of Multiple Queries And Displaying The The Reults On A Single Form

Apr 17, 2007

Im making a database about past bid results. In one table i have a list of bidders and which place they came in (along with some other information). I have 20 positions (1st place, second place and so on...), so i made 20 queries that search through the table for my company's name so we can see how many times we came in first, second, third and so on. Now i would like to bring the number of records in each query to a single form. I tried making another query to using the count feature on the other 20 queries but it keeps returning 0 as the number of results per query. But when i open each query up there are obvisouly more that 0 results in each. Is there a way i can bring up the number of results per query all on one form?

Thank you

Dan

View 7 Replies View Related

Queries :: Data Integrity Report - Multiple COUNTs In Single Query

Sep 4, 2013

I need to produce a Data Integrity report that lists the users of a separate database and the count of errors that they make, separated by error type.

Currently my query displays all the users who made errors, and the total errors they made. But I don't want the total - I want to break this number down by the types of errors that are made.

I modified the SQL to make it easier to read. How can I take the below statement and make a few expressions that count up the specific values? The field I need to dissect is "Type Error" and a couple of examples of error types are "A1" and "B1"

Code:
SELECT DISTINCTROW [Errors Table].User, Count([Errors Table].[Type Error]) AS [Errors]
FROM [Errors Table] INNER JOIN [Workcenter Profiles] ON [Errors Table].PWC = [Workcenter Profiles].PWC
WHERE ((([Errors Table].[Review Status])="Error Corrected")) OR ((([Errors Table].[Review Status])="Error Not Corrected")) OR ((([Errors Table].[Review Status])="Error Not Correctable "))
GROUP BY [Errors Table].User
ORDER BY [Errors Table].User;

I've seen it done with multiple queries joined together, but I'd like to avoid that if possible...

View 5 Replies View Related

Single Result Query

Mar 1, 2006

Hi

I am new to VBA with access. Im wanting to get the result of a query called "qLastRotaDate" into a variable called "datLastRota". The query returns a single date, it is not possible to have more than one result for this query. I have tried different variations of : datLastRota = qLastRotaDate but cannot get any to work. I'm assuming this is very simple yet I cannot figure it out as am very new to VBA.

Any help would be greatly appreciated. Sorry if this has been dealt with in previous posts but I could not find any info by searching the post.

Thanks
Mikee

View 3 Replies View Related

Queries :: Run Query Multiple Times (different Data Parameter) To Generate Single Dataset

Mar 30, 2015

I have a reasonably complex query (3 subqueries into 1 main query) which gathers data from various tables into a single dataset based on a specified date.

I now need to generate a similar dataset but across a range of dates (a month) for reporting purposes. However, I can't just adapt the query and change the parameter from a "=#<Date>#" format to a "Between #<Date1># And #<Date2>#" format

The reason being, each date has to be treated individually and has to be queried as a standalone. It's to do with the type of data I have (one-to-many relationships between tables)

So what I really need to do is run the same query multiple times, for each date in scope, then stitch all of those datasets together into one 'giant' one.

How to do that in SQL (effectively, have one query produce the dates in scope, then join that onto the other query, passing each date as the parameter - I don't even think that's possible to be honest)

The other option I can think of is to use VBA to loop through the dates in scope, then use a QueryDef object to set the parameter and read the records for each date into a Recordset object. But then I have the problem of stitching all the Recordsets together, without looping through all the fields and rows each time.

View 3 Replies View Related

Max Value For A Single Date/multiple Time/single Tag ID Combination

Oct 18, 2007

Hi, thanks in advance for any help you can offer. I've got a table that has

Date
Time
Tag ID
Power Level

throughout the day a computer listens to several tags (transmitters) and records the power level of the signal generated by the tag each 3 seconds. What I'd like to do is build a query that gives the Date, Time and Maximum Power level reading for each tag ID. I only want 1 record per tag per day

I've tried using "group by" and max in the query but this gives me all the times throughout the day.

anyway, thanks again for looking

cheers, Shaun

View 2 Replies View Related

Queries :: Conditional Query To Post Result In Field And Filter Result Records?

Mar 5, 2014

I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg

In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.

View 1 Replies View Related

Forms :: Attempting To Have Single Query Result Display In Text Box On Form

Jul 3, 2014

I have the need to display the return of my "Sum Query" to display in a text box.

I need the attached below value (40500) in the "SumofQuery" attachment, which updates every 5 minutes to display on the "StatusBoard" attachment text box.

I have been searching for a good 10 hours on how to do this and still cannot find it.

View 14 Replies View Related

Queries :: If There Is No Result In Query Need To Have Default Result Zero

Oct 12, 2013

I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.

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







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