Queries :: Trying To Summarize 2 Tables In Query

Jul 27, 2015

I'm trying to combine/summarize two tables in a query. What I've found so far is that I have to use a union query. I tried that but all I get is punctuation error.

So here is what I'm trying to do:

Code:
tblProdA
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 10
02 | 1/3/2015 | 10
03 | 1/10/2015 | 20
04 | 1/13/2015 | 20
05 | 1/17/2015 | 30
------------------------------------

tblProd2
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 5
02 | 1/5/2015 | 5
03 | 1/10/2015 | 10
04 | 1/13/2015 | 10
05 | 1/15/2015 | 10
06 | 1/17/2015 | 5
------------------------------------

qrySummary
------------------------------------
ID | Date | Production
-----+----------------+-------------
01 | 1/1/2015 | 15
02 | 1/3/2015 | 10
03 | 1/5/2015 | 5
04 | 1/10/2015 | 30
05 | 1/13/2015 | 10
06 | 1/15/2015 | 10
07 | 1/17/2015 | 35
------------------------------------

And I uploaded the sample as well.

View Replies


ADVERTISEMENT

Queries :: Summarize Income And Expenses Over Periods

Apr 1, 2013

I have some issues with summarizing in a query. My intention is to summarize income and expenses over periods

This is how it should look like.

Periods_____Total income___Total expenses
01-2013_____234__________435
02-2013_____533__________132
03-2013_____345__________853
etc

I made the following query to get the result I wanted.

Unfortunately, the result seems to be multiplied instead of summarized.

View 3 Replies View Related

Queries :: Summarize Three Fields By Work Week

Jul 17, 2013

I've got three fields - date_time, # of issues, issue reasons

I want to summarize these by work week.

So,

WW....... # of issues ...............................issue reasons
1 ..........<sum of all issues for the week>..list of all reasons entered
2 ..........<sum of all issues for the week>..list of all reasons entered
3 ..........<sum of all issues for the week>..list of all reasons entered
4 ..........<sum of all issues for the week>..list of all reasons entered
5 ..........<sum of all issues for the week>..list of all reasons entered

I know how to get the WW part - I do the datepart("ww",[Date_Time] for the expression. But how to write the query to do the other 2 parts, I'm lost.

View 1 Replies View Related

Summarize A Query / Pivot Table Style

Dec 16, 2013

So, I've recently learned that Access 2013 took a step backward and got rid of PivotTables. Unfortunately for me, PivotTables were perfect for summary reports I have to create with large data sets (some with millions of records).

What are my alternatives to PivotTables within Access 2013? I was playing around with the report builder, but it seems to retain all line items and doesn't allow me to collapse everything into a one page report.

View 5 Replies View Related

How To Summarize Fields Of Table

Oct 28, 2011

How to summarize the fields of the table this way? See the attached screen shot that show my question

I wanted to summarize the data according the values of Field1 and to get the values of Field1 and Field2 summarized separating the values with comma. How can i do that?

View 14 Replies View Related

Queries :: One Query For Many Tables?

Apr 3, 2014

How to make 1 queries for many table

example :

Table 1
name Status
john Ok
mike No
Ann OK

Table 2
Name Status
michel Ok
jony Ok
Bruce No

Queries for OK

Name Status
John OK
Ann OK
Michel OK
Bruce OK

This is just simple example, actually i have many table January until December. So when i key in data using form on every month, I want just 1 queries covering all month, so easier to check.

View 13 Replies View Related

Queries :: Using Query To Link Tables

Mar 18, 2013

I am working on creating a query to base a form on. I've done it several times in this database, but today, for some reason I cannot figure out how to do it.how I did it before but it looks like the SQL statement got wonky:

Code:

SELECT tblEmpInfo.EmpInfoID, tblEmpInfo.EmpIDFK, tblEmpInfo.JobNumberFK, tblEmpInfo.EmpType AS Expr1, tblEmpInfo.CraftCode AS Expr2, tblJobs.JobSite
FROM tblJobs INNER JOIN tblEmpInfo ON tblJobs.JobNum = tblEmpInfo.JobNumberFK;

Basically I have a look up table and all I'm doing is making it so I don't see the Cert ID it just pops up with the description.

View 2 Replies View Related

Queries :: Sum Fields From Two Tables In One Query

Jan 29, 2014

I have two tables:

Budget
Actuals

I have these fields in both tables:

MONTH, Organization Code, Account, SubAccount, AMOUNT

I want to Compare the results in a single query. However the amounts aren't correct and after a full day I cannot find the answer.

Code:
SELECT [Budget].Month AS [MONTH], [Budget].[Organization Code], [Budget].Account, Sum([Budget].AMOUNT) AS [Budget Amount], Sum([Actuals].AMOUNT) AS [Actuals Amount]
FROM [Budget], [Actuals]
GROUP BY [Budget].Month, [Budget].[Organization Code], [Budget].Account, [Budget].SubAccount
HAVING ((([Budget].SubAccount)="K08"));

View 10 Replies View Related

Queries :: Run A Query From 2 Tables According To Date

Apr 1, 2013

I have two tables and want to compile the data onto one query.

One table is a time monitor table with one record per date, with date as the primary key. It contains a number of fields where I enter the number of minutes spent on each task at the end of the day.

The second table is an appointment log. It can have multiple records for each date and the records contain a number of fields with info about the appt, one of which is the length of appt.

I have set up a relationship between the date field in both tables (was this necessary?). Is it possible to set up a query that will sort by date and grab the data from both tables, so that it will present the info from the time monitor table along with the summary of the length field from every record that fits the date range in the appointment log table? It would be good if it could also include the count for the total number of appointments that day.

It would also be good to have the info either grouped by day / week / month / yr or to have a drop down option in the query to select the date and summarise the findings.

View 4 Replies View Related

Queries :: Max ID - Create Query From 2 Tables

Jan 23, 2014

I need to get the MAX id from two tables for use in vb.net. For example:

Code:
Table1 Table2
id, customer id, customer

How do I write a query that will return the last or MAX id from both tables based on a customer criteria.

View 5 Replies View Related

Queries :: Update Query From 3 Tables

Oct 14, 2014

one of my tables.

tbl_type1
-type1_id
-PONumber

tbl_type2
-type2_id
-PONumber

tbl_bill
-billID
-PONumber
-bill_type

Now I have huge data from tbl_bill which contains mix PONumber depends on type. If bill_type is type1 then it will get data from tbl_type1 table and vise versa. the problem is tbl_type1 and tbl_type2 is changing, when someone update this table then the data in tbl_bill will not match to 2 tables.

Now, i need an update query where I can update all records of tbl_bill to match with tbl_type1 and tbl_type2 PONumber.

View 3 Replies View Related

Queries :: Referencing Other Tables In A Query?

Dec 4, 2014

I have a split database that is working pretty well, but I've been asked to add a functionality to it. I have a table, product1, that contains basic product information. One of the values is a "class", in that if a product was created at a certain time, it fits into a class; the other value in this table that is applicable is the time the product was created.

I have another table, class1, that lists the beginning and end times of the class periods and the class numbers.

So, my users will "ingest" a product, including the time the product was created. I need the query (i think so anyway) to fill in the "class" value in product1, by determining which class it is in by seeing which class beginning and end time it is between in class1 and using the class number found in class1.

View 1 Replies View Related

Queries :: Count Query From Two Tables

Nov 5, 2013

I have TableA with

CityCode (indexed, no duplicates, Primary Key)
CityType (duplicates ok)

TableB, with
CityCode (indexed, duplicates OK)
StreetCode (indexed, No Duplicates, Primary Key)

A One to Many relation between Tables on City Code

In need to feed a cell table with the value of the number of streets that are in "Type1" cities

View 5 Replies View Related

Queries :: Update Query Using 3 Tables?

Sep 1, 2014

Can't figure the SQL to UPDATE a table (tblAccomResv) with columns: AccomID and DtBooked. It will be an empty table.

I need to populate it with one record for each record in tblAccomodations (ID) by each record in tblEventDts (EventDts).

Like:
Room1 Dt 1
Room1 Dt 2
Room2 Dt 1
Room2 Dt 2
etc.

View 5 Replies View Related

Queries :: Combining Tables In A Query

Jul 9, 2014

I have an invoice form that is from tblInvoice, and on that form i have 5 subforms all tied to 5 tables, tblRepair1, tblRepairs2, etc. When we get service done at a shop the user can enter up to 5 repair types, which all five tblRepairs are joined to the InvoiceID of the tblInvoice.

the issues is one user might put oil change in repair 1 and tire rotation in repair 2 and the other 3 repairs not used. another user may use all 5 repairs and put oil change in the repair 5 field. What i want to do is query a repair type, Oil change, and get every InvoiceID record that has oil change in it regardless of what repair table it was stored in, or in other words regardless of if it was entered in repair1 or repair5.

i have tried to "step down" the criteria referencing the cbx on the search form but if queries blank. I only want the records that have an oil change in them but still want to see all the repairs that were done with it.

View 4 Replies View Related

Queries :: Design Unmatched Query On 2 Tables

Oct 22, 2013

I need to design an unmatched query on 2 tables to identify employees names that exist in 1 table but not the other (or names are misspelled, etc.). The tables exist in on a server and I do not have the ability to change either. Table 1 has the following structure:

EMPLOYEE NAME: Doe, John ID-12345
Table 2 has the following structure:
LAST NAME: Doe
FIRST NAME: John

Since the field names are not equal, the unmatched query wizard (as far as I know) will not work. I've tried using "NOT LIKE", LEFT JOINS, RIGHT JOINS, NUll Values, etc. in the SQL but nothing has worked as yet.Here's the SQL I've been working with but this returns all values (haven't figured out why):

SELECT DISTINCT [Table 1].[EMPLOYEE NAME]
FROM [Table 1], Table 2
WHERE ((([Table 1].[EMPLOYEE NAME]) Not Like "([Table 2].[LAST NAME]*"));

View 2 Replies View Related

Queries :: Join Data Of Two Tables Using Query

May 3, 2015

I have two tables with name of accone and the second with the name of acctwo. These two tables are same according to number of columns and also same according to data types and also same according to the column names just the data are difference and also one column (attribute) with the name of ID is same in both tables. Their is a primary key relationship (one-to-one) between these two IDs. I need a query that can combine the data of both of them and can be updated using query. I mean that data of first table and second table must become under one same column not two columns one for first table and second one for second tabel.

View 4 Replies View Related

Queries :: Can Have Two Tables Linked To One Search Query

Nov 28, 2013

Can we have two tables linked to one search query? I've some data that is confidential and woul like to have them in two different table. When I search for it using query form, only the relevant table results will be shown?

View 4 Replies View Related

Queries :: 2 Tables To Query To Form Then Back?

Oct 1, 2013

I have a database for contacts and inventory, and im having a issue with the contacts part of it.

I have 2 tables, 1 table is a list of contacts with their name, contact information and the facility they work at. The other table, carries all the information for the facility(the tables are in a one to many relationship with this)

I have a query that brings them together and displays them on a spreadsheet form. This worked when it was all one table, but with the 2 tables, you cant edit stuff in form and have it go back to the tables . what would be the best way to make it editable. I separated the forms out like this so that if a facilities name changed, it could be fixed easily throughout the database.

View 1 Replies View Related

Queries :: Creating A Query With Self Join Tables

Feb 11, 2014

I have a table called MiscORders where all the orders are tracked. There is an ID, order number , other fields and ParentorderID fields in it. Whenever an order is modified they create a new order with ordernumber and have the previous order number as the parent. The next time it is amended or closed another order is created with the parent order attached. Here is some sample data

Order ID OrderNum Parent order id
1 MISC 2013-10
2 MISC 2013-10A MISC 2013-10
3 MISC 2013-10B MISC 2013-10

Now I am creating a query that shows the lifecycle of the order

MISC 2013-10 Issue and deadline date MISC 2013-10A issue and deadline date MISC 2013-10B issue and deadline date.I tried to create a query using self joins to the same table. SQL is attached

SELECT MISCORDER.OrderNum, MISCORDER.[Date Issued], MISCORDER.[Deadline Date], MISCORDER_1.OrderNum
FROM MISCORDER LEFT JOIN MISCORDER AS MISCORDER_1 ON MISCORDER.OrderNum = MISCORDER_1.RescindedOrderID
WHERE (((MISCORDER.OrderNum) Not Like '*A' And (MISCORDER.OrderNum) Not Like '*B' And (MISCORDER.OrderNum) Not Like '*C' And (MISCORDER.OrderNum) Not Like '*D') AND ((MISCORDER_1.OrderNum) Not Like '*B'));

How can I put a condition on the table to show only records with order num ending with A. It is not working if i use it in the where condition..

View 1 Replies View Related

Queries :: Append Query In Different Levels Of Tables

Dec 17, 2013

I receive the data from engineering team lets name it as MAster database( in DB2 database - Read only ) and then i want to assign the data to each member and staus will be updated by them.So my Idea is upload the data from Engineering team (MAster database) to my (Local database), i think of using Append query if there is any changes in engineeiring data after i append the data then i can use the Update query to modify the data.

But once i assign the job to every member from my Localdatabase, i want it to stored in a data base with their name because it is a huge volume data and it will go for years.The bottleneck is If i use the Append query here, the changes from Masterdatabase to Locadatabase will not reflect in Team members database? is any solution for this or i have to use the Update query in Localdatabase and also in Team memers database?

View 1 Replies View Related

Queries :: Delete Query From Two Linked Tables

Jul 9, 2015

I have two linked tables (Unique_ID)

Table1 has a record of unique tasks.
Table2 has a list of people to do those tasks

They are linked by a Unique_ID (one to many - Table1 to Table2).Sometimes there can be an instance where there are Unique_ID's in Table2 but the reference Unique_ID is missing from Table1.

I have a select query that can show me these like so.

table2 table1
Unique_ID Unique_ID
Like * Not Like *

I can open the query and simply delete the records fine however i wish to automate this.What I want to do is delete the unrefenced Unique_ID's from table2.I have tried to just convert this select query into a delete query but it asks me which table I wish to delete and I cannot see where to enter this information.

View 3 Replies View Related

Queries :: Two Tables Fields Into One Query Field?

Feb 24, 2015

I have two tables: tbltasks & tblsafety

Both tables have a date field in, one table keeps tracks of my tasks the other the expiry date of some safety checks.

I currently have a timed pop up that looks at dates within a table that are within 30 days from now, if there are any dates the reminder pops up.

The problem is I want the pop up to look at two sepearte columns in two different tables, so I figured it would be easier to create a query combining these dates and just ask the pop up to look at that query date column.

I want to create a query that very simply lists all the dates in one column combined from both tables.

View 3 Replies View Related

Queries :: Search Across Multiple Tables With One Query

Apr 18, 2013

Is it at all possible to search across multiple tables with one query. I have a database with about 15 tables and my users would like to be able to search for text across all the fields in all the tables. I have a simple find record command on each table, but in case they are not sure which table to search i would like to offer a search function which in theory searches the whole database.

I have 15 tables (eg witnesses/policies/activities etc), each with different fields (eg name, address/ policy decision, policy reason/ activity undertaken, activity person etc etc) . In total i would like to be able to search about 50 fields. All i need returned from the search is the record ID from the relevant table, or anything to simply identify the record & relevant table. And then they could go to the correct place to look the record up.

I have been looking for an answer for a while. I have seen queries where you enter the parameters against each field going down a row each time for criteria being "OR" each time, but there are a limited number of rows on a query design so i run out of rows.

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

Queries :: How To Query Two Tables And Compare Results

Apr 18, 2013

I am putting together a database for work, I have a table that is used to refer to (this table is part of our production system) in this table it lists products run, dates run and who has run.

I have the training records data table, this lists; who, machine and status: "trained" "refresh required" " not trained"

I want to build a query so that it searches the production table for the last 6 months worth of records & if an operator that is in the training table (WHO) as status (trained) is not in the production table it outputs a "to be refreshed" report or amends the records from trained to "refresh required" ...

View 1 Replies View Related







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