Combining Records From Two Queries
Dec 10, 2007Can I use left and right outer join at a time? Means I want all records from both the table, and it should put 'null' whenever value is absent.
View Replies
ADVERTISEMENT
Queries :: Combining Like Records In Union Query
Nov 25, 2013How can I add the resulting records from a union query. The results of the union are something like this:
Quantity Item
2 Cats
3 Cats
1 Dog
4 Mice
What I need to display is
Quantity Item
5 Cats
1 Dog
4 Mice
How can I get the query to "do the math"?
Combining Records
Jun 22, 2007I'm modifying a database for someone in our marketing department. The DB tracks shirts that are given to employees.
The area I'm having a problem with has to do with the shirt style.
The current categories offer:
Long Sleeve (White)
Long Sleeve (Black)
etc..
I want to combine those two so they fall under the simple category "Long Sleeve"
The problem I'm having is that when I do a find/replace, it will allow me to change all of the Long Sleeve (White) to Long Sleeve, but then when I go to change Long Sleeve (Black) to "Long Sleeve", it creates duplicate entries because the same order contained x amount of Long Sleeve (White) and x amount of Long Sleeve (Black).
Is there an easy/ automated way to combine these results into "Long Sleeve" and add the quantity fields together to show the total amount of shirts ordered?
Combining Records
May 7, 2007Hello,
I seem to have a similiar problem to others and I tried to read through this forum, but I am not well versed in the code and I frankly find some of the explanations way to complicated. I was hoping someone can help me with a problem. I have data that looks like this:
Name Comments
J Smith 1/1/07-ATO
J Smith 1/10/07-UAU
S Smith 1/13/07-ATO
D Smith 1/2/07-OT
D SMith 1/3/07-OT
D Smith 1/4/07-ATO
I want it to look like this:
Name Comments
J Smith 1/1/07-ATO, 1/10/07-UAU
S SMith 1/13/07-ATO
D Smith 1/2/07-OT, 1/3/07-OT, 1/4/07-ATO
ANy suggestions?
Combining Records
Aug 18, 2005Hi
Currently in our database we several companies entered several times. Due to different branch addresses. What we are looking to do is create a form that combines displays all the addresses related to that customer name.
Does anybody know of a way we can do this?
Regards
Paul
Queries :: Method For Combining Crosstab Queries With Same Criteria From Multiple Fields?
Dec 2, 2014I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
Combining Records From Two Databases
Mar 9, 2007Is there an easy way to combine records from two separate databases with the same data fields?
View 3 Replies View RelatedCombining A Number Of Records Into One.
Jan 17, 2006I have a table that contains a number of email addresses. I need to combine a number these into a single record to allow me to email a report to a number of different recipients depending on the client the report is for.
At this point I’m not even sure were to start.
Combining Records From 2 Tables
Jan 3, 2008Hello all,
I have two MS Access tables; table1 contains installing records (fields = serial, installdate) and table2 contains de-installing records (fields = serial, deinstalldate). Tables are structured as followed:
Table1:
serialX installdate1
serialX installdate2
serialX installdate3
Table2:
serialX deinstalldate1
serialX deinstalldate2
I'd like to combine the above two tables to get:
Table3:
serialX installdate1 deinstalldate1
serialX installdate2 deinstalldate2
serialX installdate3 null
Is it possible? Right now, whenever I joined the two table on serialX I got 2 deinstalldate's for each installdate, e.g.:
serialX installdate1 deinstalldate1
serialX installdate1 deinstalldate2
serialX installdate2 deinstalldate1
serialX installdate2 deinstalldate2
..........
Thanks in advance for your time and help! I'd appreciate it!
Best regards,
Andy
Queries :: Access 2007 / 2010 - Combining Two Queries Into One?
Jan 24, 2014I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.
The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.
I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.
I am running ACCESS 2010 Tables are ACCESS 2007.
OS is Windows 7 Ultimate.
I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.
Queries :: Combining Data Results From Multiple Queries
Sep 30, 2014I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
Queries :: Combining Queries Without Matching ID Values
Feb 2, 2015I have two queries:
1. a sum of payments by individuals
2. a sum of charges of individuals.
I want to combine these queries to create a report that shows the sums for each person of the charges and payments and calculate the balance. However, it is only showing me those individuals that have both a charge sum and a payment sum, while some individuals will only have a charge sum. How do I get those individuals to show and show with a zero payment balance if they are in my charge sum query but not my payment sum query and vice versa (some may have made a prepayment even though they have not been charged yet).
Queries :: Combining Two Queries Without Union Query
May 7, 2014Im trying to join two queries as I am unable to use just a single query but I cant use a union query as the query fields aren't exactly the same.
Both queries have a PO_Detail field as every PO has a PO_Detail number assigned to it. 3 of the same records are in both query results but one query is missing the other two results.
If I create a join between the two queries based on this field I don't get all the results. Unfortunately I cant upload the database as it has sensitive data which would take me ages to clear out but I can show a picture of the results.
Combining Multiple Columns Into One Longer Column (without Combining Fields)
Jul 17, 2013I trying to combine three columns that I have into one column without combining fields.
Currently what I have:
(see image below)
What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc
The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.
Combining Queries
Dec 13, 2005hi
i want to comibine some queries into one table
the first quierie contains like 6 records and the second one contains 3 records
the problem is is that when combining these columns into the one query the second contains more then 3 records when it should be only displaying data for the 3 bits ?
Combining 2 Queries
Jan 10, 2008Thank you in advance for your assistance.
I've read through the other threads but can't seem to find anything that is simple (not an experienced SQLer so can't really tweak too much) and that would work for my problem.
I have a Project table linked to an Invoice Table (1 to many respectively)
The Invoice table linked to a Transaction table (1 to many respectively). 1 invoice can be paid out to many different cost centers and amounts.
I also have a Reclass table that is linked to the Project table.
Basically Invoices can be processed to multiple cost centers originally. But can also be reclassed at a later date to other cost centers.
The way the database is setup is so that if there are 10 invoices each having multiple records in the Transaction table (charging 1 invoice to multiple cost centers) AND we need to reclass them. We don't want to reclass each an every invoice and transaction record. It setup so that we can take the total invoiced amount for the project (sumed up all the invoices for that project) and reclass one total amount.
The problem is now I have 2 separate "financial" tables. One for the original invoice payment transaction (Transaction table which has the original cost centers and amounts) and a Reclass table with the "new" cost centers and amounts.
I need to combine them so that the financial reporting will reflect the reclasses (moving dollars from one cost center to another).
I tried creating a separate table (All Financials Table) that I can append the "original" Transaction table and Reclass table to use for reporting. I have this working but I can forsee possible problems going forward (i.e. user may append multiple times by a button click).
Is there a simple way to do a combined query on both the Transaction and Reclass table. Both queries have the following: Project ID, Cost Center and Amount. The problem I'm having is that if one table has 10 records of say cost center 123456 and the other has 3 records of 123456. The query ends up giving me 30 records.
I know my explanation is long but I think it will help paint the bigger picture.
Thanks again for reading this!
Combining Two Queries.
Jan 29, 2008I have a query called Spans1 that calculates the difference between dates:
SELECT [Rasp].[Issue Number], [Rasp].Issues, [Rasp].Status, [Rasp].Date_Received, [Rasp].Date_Resolved, DateDiff("d",[Date_Received],[Date_Resolved]) AS Span
FROM [Rasp]
WHERE ((([Rasp].Status) Like "*" & [Enter status]));
I have another query called Average_Span that gives me an overall average number of days between the dates in the above query:
SELECT Format(Avg([Span]),"0.0") AS Expr2
FROM Spans1;
Is there a way to combine the 2 so that I get the spans and an overall average? Any help is greatly appreciated.
Combining Two Queries
Nov 5, 2004I have a need to combine two queries into one so I can run it from a function. I have tried everything to no success.
Anyone have any ideas?
Query one [find m owner] this restricts the table to only those records owned by "m":
SELECT [Table1].[PK], [Table1].[dog], [Table1].[owner] as tempList
FROM Table1
WHERE ((([Table1].[owner])="m"));
Query two this deletes the latest record added by "m":
DELETE [find m owner].PK
FROM [find m owner]
WHERE ((([find m owner].PK)=(select top 1 P.PK from [find m owner] as P Order By P.PK Desc)));
-------------------------------------------------------------------------------------------------------------------
I tried copying
(SELECT [Table1].[PK], [Table1].[dog], [Table1].[owner] as tempList
FROM Table1
WHERE ((([Table1].[owner])="m")))
everywhere [find m owner]
exists in the second query but received syntax errors
this is the combined query
DELETE (SELECT [Table1].[PK], [Table1].[dog], [Table1].[owner] as tempList
FROM Table1
WHERE ((([Table1].[owner])="m"))).[PK]
FROM [SELECT [Table1].[PK], [Table1].[dog], [Table1].[owner] as tempList
FROM Table1
WHERE ((([Table1].[owner])="m"))]. AS [%$##@_Alias]
WHERE ((((SELECT [Table1].[PK], [Table1].[dog], [Table1].[owner] as tempList
FROM Table1
WHERE ((([Table1].[owner])="m"))).PK)=(select top 1 P.PK from (SELECT Table1.PK, Table1.dog, Table1.owner FROM Table1 WHERE (((Table1.owner)="m"))) as P Order By P.PK Desc)));
Combining Queries
Oct 6, 2006Hi
I have a query that uses Count to count the number of Attendances in a register in which there are the letters / Present, A = Absent, E = Excused, L = Late
I have another query that calculates the total possible number of attendances
I am now trying to combine the two so that it will calcuate the Percentage Absent, Percentage Present etc
Can this be done in one query, can the two queries be joined together or do you have to use vba in a Form to
extract the information from the two queries.
thanks for looking
Nick
Combining Records With Attachments From Separate Computers Into A Master Database
Jun 21, 2013We're a small shop building a modest database to keep track of our product listings on eBay. Two people on non networked computers are doing the data entry into cloned copies of our database. This includes attached photos for each record. At the end of each week, we want to combine the records created separately into the master database on my system.
It seems like it would be simple enough to just cut and the paste the records from the two databases into the identical master. The problem I'm anticipating concerns the attached photos.
Some strategies are to transfer the records from the data entry computers to the master database while keeping the attached photos intact.
Is there an effective, efficient way to set this up or would it be best to use the OLE format for the pictures instead of attaching them (which I prefer)?
Combining Search Queries
Mar 5, 2008Hi there,
I will need to create a way of searching through products in a bookstore by product name, Author or by ISBN. Right now, I have designed 3 seperate queries, but to get more marks in my coursework I would have to combine the 3 queries to allow the user to be able search the three fields in the follwoing way:
Search by: Product Name AND/OR Author AND/OR ISBN
in other words, the user could have the option of searching by either product name, author or ISBN OR they could search by each individual criteria, or they could use a combination of two (e.g. Product name and author).
Could someone please help as i gotta get this sorted
Thanks
Dan
Combining Cross Tab Queries
Jan 25, 2006I have a number of cross tab queries which I run on a weekly basis, each of which is running from a different date field in the database (e.g. one query for orders received based on 'Received Date', another for orders complete based on 'Received Date'. On a weekly basis I have to open each query in design view, edit the start and end date for the week and execute the query. It would save significant time if I could enter a date range once into a form and run the series of queries based on these parameters. Any thoughts?:) :)
View 1 Replies View RelatedCombining Queries With Forms
Feb 18, 2006I'm new to the forum as well as I am new in using MS Access, so please don't lough :) I wasn't even sure whether this is the right subforum to post to....
Anyway here it goes....I have this really big database (nearly 5000 records) and I was wondering how could I make a query, perhaps outside of the Design View, and making it appear like a form e.g. let's say I want the query to display in form style (or even report...) the rest of the fields concerning a certain record when I just give the ID number of a person.
Should I turn to code solution?
Help anyone?????
Combining 2 Tables Into 1 Using Queries
Nov 13, 2006I've been asked to make a DB for our production. We have 2 sources, of which have similar field names and collect completely different data, but can not be linked. I don't want them to be linked either, I don't think. The only thing I want to do is to take the 2 tables I have from my 2 sources and combine them into one. I'm using the first table in a make table query and then creating blank fields in the query also, so I can append the data from the second source in the "all production" table. The only problem I'm running into is that I can only get to 26 fields in the make table query, I need 31 fields to get everything. I tried creating a new query and not using any tables, and I was able to get to 16 fields. Is there something I'm doing wrong, or is there another way I can get a table created will all fields I need and append the data from both sources? I would need to be able to re-run this daily which is why I was trying to do it through a query. Thanks for any help/ideas. It's greatly appreciated!
View 5 Replies View RelatedCombining Queries. Complex!
Feb 12, 2007Alrighty...
My DB contains the following tables:
VacTab - Keeps track of crew members on vacation and people covering their shifts.
CrewRost - List of all the crew members and what crew they belong to.
CrewSched - The complete schedule of what crew is working which shift (Currently programmed out until Feb 2008)
Now, I need a query which totals regular crew hours for individuals and the overtime hours they cover for other crew members on vacation. And puts the overtime hours in the correct day/shift.
I have a working query which equates how many hours an individual works with their crew, based on the CrewSched Table. This was rather complex, because we work a combination of 8 hour and 12 hours shifts.
I also have a working query which puts the overtime hours of each crew member into the correct day/shift that they cover for crew members on vacation.
What I need is a way to combine these into a single report, where I can use conditional formatting to highlight the overtime hours. The problem is, if I use a union query, the report will treat these values as such, and not highlight the overtime hours.
If anyone has an idea as to where I can start, it would be greatly appreciated.
Thanx :) ,
Matt
Problems Combining Two Queries
Aug 27, 2007Hello again,
I am trying to have users be able to see and enter comments on a form along with results from a query. My database looks like this: http://img172.imageshack.us/my.php?image=howwillthisworkvv3.jpg
the "comments" table is what i'm having problems with. Right now i have a query where a user selects a product and any number of funds and it returns the Counterparties that meets the criteria based on the "Combine" table. This works great, but due to the combine table using the counterparty entity I can't figure out how to relate the comments. I am using counterparty entity in the combine table because some users will need the more specific information.
I have tried linking the Comments table to the counterparty table and the products table, but this poses the problem of updatablility and entering too much useless data. If i did it this way each entry of a comment could have to be repeated 5-10 times(for each entity). Also if there are no entries for a certain counterparty any query i make will either come up with nothing or it just repeats the comment entries i do have.
I have also tried putting the comments in the Combine table, but this poses a similar problem because of multiple entities per counterparty.
So, what I'm looking for is a way to match up comments to a single product and a list of counterparties in a continuous results form. Also i want the user to be able to edit each comment ON the results form(does this mean it has to be directly linked to the table?).
I tried researching updating multiple similar fields(one entry updates all the entities of a counterparty for a product), which may solve the problem, but i found nothing. Any ideas?
Thanks!