Combining Queries With Forms
Feb 18, 2006
I'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?????
View Replies
ADVERTISEMENT
Dec 2, 2014
I 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]...
View 2 Replies
View Related
Jan 24, 2014
I 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.
View 7 Replies
View Related
Sep 30, 2014
I 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.
View 4 Replies
View Related
Feb 2, 2015
I 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).
View 5 Replies
View Related
May 7, 2014
Im 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.
View 10 Replies
View Related
Jul 17, 2013
I 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.
View 14 Replies
View Related
Dec 13, 2005
hi
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 ?
View 2 Replies
View Related
Jan 10, 2008
Thank 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!
View 4 Replies
View Related
Jan 29, 2008
I 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.
View 6 Replies
View Related
Nov 5, 2004
I 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)));
View 2 Replies
View Related
Oct 6, 2006
Hi
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
View 2 Replies
View Related
Mar 5, 2008
Hi 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
View 3 Replies
View Related
Dec 10, 2007
Can 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 1 Replies
View Related
Jan 25, 2006
I 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 Related
Nov 13, 2006
I'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 Related
Feb 12, 2007
Alrighty...
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
View 2 Replies
View Related
Aug 27, 2007
Hello 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!
View 4 Replies
View Related
Sep 24, 2007
Combining Queries
The goal was to track equipment downtime, by each piece of equipment, but the WC might have multiple downtimes on multiples equipment each day. So you end up with equipment numbers in different parts of the table.(mach # 1234) I’ve been trying to figure out how to combine all the data without much luck. Joining Mach# and summing the times of minutes in the different fields. I tried making a new table and appending or updating, but that did not seem to work. The person entering data has six places on the form, so she can enter mach# in any of the six places.
Thank you for any ideas.
Example:
WC 1 has equipment problems on day 1
Equip # MC-1234 Mins 15 (stored in table as Maint1Mach and Maint1Min)
Equip # MC-4567 Mins 20 (stored in table as Maint2Mach and Maint2Min)
WC 1 has equipment problems on day 2
Equip # MC-4567 Min 90 (stored in table as Maint1Mach and Maint1Min)
Equip # MC-7890 Min 20 (stored in table as Maint2Mach and Maint2Min)
Equip # MC-1234 Min 20 (stored in table as Maint3Mach and Maint3Min)
Thank you
ATTCAHMENT:
View 9 Replies
View Related
Jun 20, 2005
I just recently figured out how to count how many jobs a CSR has submitted and how many errors a CSR has made. What I need to do now is combine these two queries and figure the percentage of jobs correct that a CSR has submitted. I tried to combine these to queries in a report and figure it out that way, but that didn't work. When I combined the two queries I lost the individual information in the queries. I tried to combine the two queries but it wouldn't let me. It said that I was trying to combine the fields of a table and a query and that I should use one or the other.
Can anyone help me?
learnasugo
The excel file is how they would like the report to look
View 9 Replies
View Related
Nov 21, 2013
I have the following 3 queries, one does one thing, the 2nd one another thing and the 3rd merges both. But when one of the queries returns a null value the merged query seems to give me a null result even though there are values in the second query.
Q1:
Code:
SELECT Tasks.Resource_F, Tasks.Resource_LFROM Tasks, CachedDates
WHERE (((Tasks.Start_Date)<[CachedDates].[SDate] Or (Tasks.Start_Date)>[CachedDates].[EDate]) AND ((Tasks.End_Date)<[CachedDates].[SDate] Or (Tasks.End_Date)>[CachedDates].[EDate]));
Q2:
Code:
SELECT DISTINCT Employees.First_Name, Employees.Last_NameFROM Employees LEFT JOIN Tasks ON Employees.ACF2_ID = Tasks.Resource_ACF2ID
WHERE (((Tasks.Resource_ACF2ID) Is Null));
Merged Query:
Code:
SELECT DISTINCT Employees.First_Name, Employees.Last_NameFROM Employees, Q2_NamesNotInTasks, Q1_AvailableNamesInTasks
WHERE (((Employees.ACF2_ID)=[Q1_AvailableNamesInTasks].[Resource_ACF2ID] Or (Employees.ACF2_ID)=[Q2_NamesNotInTasks].[ACF2_ID])) OR (((Q1_AvailableNamesInTasks.Resource_ACF2ID) Is Null));
View 2 Replies
View Related
Aug 24, 2006
I have created two queries which calculates a total. I want to add the values of the two queries in a third query to give me a grand total.
When I try to create this third query, it gives me an error saying that the fields cannot be used from two different queries. So I'm just wondering whether this is possible or is there any other way of doing this.
View 1 Replies
View Related
Mar 12, 2007
I have a table with a field called "description"
another field in fame table called "length of cable"
In description field it contains among other things, the length in it . It may be at various locations in the test data in the field. The length may be in the form xft or xxft xx foot xxfeet xx feet..
I have another field that I need to put the length in a standard form like xx feet
(The field will have different units in it later not just feet.)
I am using update query
with
criteria of Like "*3ft*" Or Like "*3 foot*" Or Like "*3 ft*" Or Like "*3 foot"
with update to (of the length of cable field ) set to 3 Feet
The data has many lengths
1ft
2ft
3ft
4ft
5ft
6ft
7ft
10ft
12ft
14ft
15ft
20ft
25ft
and one....
I would like to automate it to one query and hopefully avoid the problem I have when doing it as is, *5ft* also finds 25ft and 15ft incorrectly for the goal of the update. same on 12ft 14ft
' and " are the symbols for inches and feet.
Also some of the data is in the form xx' or xx" I hit a wall on this due to the ' character
need to do the same to that as well.
Any suggestions '''
before I lose any more hair ...
View 6 Replies
View Related
May 12, 2013
So in my example you have a Weld (tblWelding_WeldingID) and each weld can have more than 1 consumable (tblconsumables_consumableID). have a third table that links the two, trackID,consumableID, weldingID)
What I need to be able to do in a report, is under the heading COnsumable, list the 1 or many consumables.
Also you select the consumable from a combo box..... if that just makes it that much harder...
View 2 Replies
View Related
Sep 16, 2014
I have 2 queries serving useful routines, one to filter out duplicate addresses for a mailing label routine, the second to convert tick boxes into Y/N answers for a Tab Delimited Text file export.can I combine the 2 SQL statements in the 2nd query without amending them in any way.
The SQL statements are;
FROM [Mail List] AS a
GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-News List], a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1]
HAVING (((a.[e-News List])=False) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1]
From [Mail List] as b
Where b.[Address 1] = a.[Address 1]
Group By [Address 1])));
[code]...
View 7 Replies
View Related
Jan 21, 2014
I know how to concatenate fields in a query but have not done it with memos before. I have three memo fields and I want each to show up in one field with a bullet in front of each. Memo1 may be empty and memo2 and memo3 may have something or memo 3 may have some text but the others are empty so I need to be able to list the memos without the empty spaces. Is a query the best place to do this or in the report and how do I do it?
View 4 Replies
View Related
