Hi~! I'm new in access vba and sql... hopefully my question won't be too dumb.
Basically i'm working on a report... which the report data/information was calculated in few different queries(which i created) from few different tables. in simple, each of the data/information was from a query... i got about 10+ data/information to display, that's why i created 10+ queries.
I done all the calculation in queries, but my problem is... how to add all these queries into 1, so i can directly create a report from the 1 query.
most of the queries was using union all/union... i tried to use union/union all to put all these queries together, but it won't works... because each of the queries using different tables/fields... even though i add those table/fields into the queries... it didn't work as well...
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
Am trying many times in UNION query but its not working because there is different field names and only some fields are matched. So I need to both query's are combine in 1 query.
Above both queries are already combined in UNION query as (Customer Credit Transaction Final) its not a problem.So now I need to do combine the above Union query Customer Credit Transaction Final & ReceiptformQry.
The both query details:
Customer Credit Transaction Final SELECT BillBook1.TID, BillBook1.BILLNo, BillBook1.BILLDate, BillBook1.Customer, BillBook1.BillMode, [BillBook1 Vat Details].[TOTAL Rs] AS [CC Amount] FROM BillBook1 INNER JOIN [BillBook1 Vat Details] ON BillBook1.TID = [BillBook1 Vat Details].TID WHERE (((BillBook1.BillMode)="Credit")); UNION ALL
[code]...
Above fields are need to merge in Union query or otherwise. and remaining fields are needed to show separately.
SELECT DISTINCTROW tbl_members.surname, Count(tbl_years.year) AS CountOfyear FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member GROUP BY tbl_subscriptions.subscription_fee, tbl_members.surname HAVING (((tbl_subscriptions.subscription_fee)=0));
This query displays a list with the surname of the member and the Count of the Years he/she did not pay the annual subscription, hence where subscription_fee = 0
TRANSFORM Sum(tbl_subscriptions.subscription_fee) AS SumOfsubscription_fee SELECT tbl_members.surname, tbl_members.name, tbl_members.mobilephone FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member WHERE (((tbl_years.year)>Year(Date())-"6")) GROUP BY tbl_members.surname, tbl_members.name, tbl_members.mobilephone PIVOT tbl_years.year;
This query displays a list with the surname, name, mobile phone of the member along with the money he/she paid for the last 5 years as you can see from WHERE (((tbl_years.year)>Year(Date())-"6"))
My question is: is it possible to combine those 2 lists and have one where all of the following columns will be listed? Surname, Name, Mobilephone, Count of years with 0 payment, a column for each year of the last 5
I have two queries that I am interested in combining into one if possible. I'm trying to learn Access and SQL on-the-fly, so feel free to point out any noob mistakes I am making.
The first query simply pulls certain records from a table:
SELECT Sensor5.LaneName, Sensor5.SensorTime, Sensor5.Speed, Sensor5.Volume FROM Sensor5 WHERE (((Sensor5.LaneName)="NB1" Or (Sensor5.LaneName)="NB2" Or (Sensor5.LaneName)="NB3") AND ((Sensor5.Volume)>0) AND ((Sensor5.SensorDate)="4/17/2007" Or (Sensor5.SensorDate)="4/18/2007" Or (Sensor5.SensorDate)="4/19/2007" Or (Sensor5.SensorDate)="4/20/2007" Or (Sensor5.SensorDate)="4/23/2007" Or (Sensor5.SensorDate)="4/24/2007" Or (Sensor5.SensorDate)="4/25/2007" Or (Sensor5.SensorDate)="4/26/2007" Or (Sensor5.SensorDate)="4/27/2007"));
The second query then takes averages and sums from this first query, grouping by a third field (SensorTime). This results in weeks of data being compiled into a single record for each time interval in a 24-hour period. See below:
SELECT SpeedWeekday5NB.SensorTime, Avg(SpeedWeekday5NB.Speed) AS AvgSpeed, Sum(SpeedWeekday5NB.Volume) AS SumVolume FROM SpeedWeekday5NB GROUP BY SpeedWeekday5NB.SensorTime;
Is there any way I can streamline this process by combining the two queries into a more complex single query, or should I leave things as-is? Any advice is much appreciated!
I would like to take these two queries and combine them into one if possible. This is the first query:
SELECT DISTINCT [LINE 2].[CASE ID] INTO [TABLE 1] FROM [LINE 2] INNER JOIN NOLDBA_OBLIGATION ON [LINE 2].[CASE ID]=NOLDBA_OBLIGATION.ID_CASE WHERE (((NOLDBA_OBLIGATION.AMT_PERIODIC)>0) AND ((NOLDBA_OBLIGATION.DT_END_OBLIGATION)>#6/30/2007#) AND ((NOLDBA_OBLIGATION.DT_END_VALIDITY)=#12/31/9999#));
And this is the second query which is based on the results of the first query:
SELECT NOLDBA_CASE_ROLLUP.ID_CASE INTO [TABLE 2] FROM [LINE 2] INNER JOIN (NOLDBA_CASE_ROLLUP LEFT JOIN [TABLE 1] ON NOLDBA_CASE_ROLLUP.ID_CASE = [TABLE 1].[CASE ID]) ON [LINE 2].[CASE ID] = NOLDBA_CASE_ROLLUP.ID_CASE WHERE ((([TABLE 1].[CASE ID]) Is Null) AND (([NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_OWED]-[NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_PAID])>0)) GROUP BY NOLDBA_CASE_ROLLUP.ID_CASE;
Can this be done and if yes can someone show me how? Thanks
Code:SELECT DISTINCT Team.TeamLeadName, RawData.OutlineNumber2FROM RawData LEFT JOIN Team ON RawData.TeamLeadNumber = Team.TeamLeadNumber;
Code:SELECT RawData.OutlineNumber2, FormatPercent(Avg((RawData.Finish-RawData.Start)/(SELECT SUM(RawData.Finish - RawData.Start) FROM RawData))) AS DaysSpannedProgressFROM RawDataGROUP BY RawData.OutlineNumber2;
the output of query 1 is TaskName | Outlinenumber2
the output of query 2 is TeamLeadName | Outlinenumber2
and the output of query 3 is Outlinenumber2 | DaysSpannedProgress
I would like to have one query that has all that info... TeamLeadName | Outlinenumber2 | TaskName | DaysSpannedProgress
I have ONE table with data that looks like: Username1, Username2, Date, EventType, Money (basically)
I have a query that combines all the Username1 entries together. It sums the money, and COUNTS the times it was entered into the database.
I have another query that does the same, but uses Username2 as the main filter.
The reason I have the two queries, is that not every entry in the main table has a Username2 in it. But I want to make sure I include those entries that have a Username2 in the totals and counts.
Im not sure if this makes sense to anyone, but if there is a way to combine the two queries I have, it would help to have some guidance. I don't have a clue how to do it in ASP (if possible) and don't know what to try inside ACCESS as I experiemnt around.
Thanks for any help or guidance. I am quite the newbie to ASP scripting. Thanks again.
I have three queries that make a training list based on a person's role, team, and ad-hoc exceptions. The personnelID field is filtered by a listbox on a form. Each of these work great on their own (nice!). Now I want to combine them. I made the below union query that works... however when it is run, I am prompted to enter the personnelID again. This prompt only happens once. Do I need to incorporate a qhere statement somewhere, even though each of the individual queries have them already?
SELECT First_Name, Last_Name, TrainingTitle, PersonnelID FROM qryPersonnelTrainingByRole UNION ALL SELECT First_Name, Last_Name, TrainingTitle, PersonnelID FROM qryPersonnelTrainingByTeam UNION ALL SELECT First_Name, Last_Name, TrainingTitle, PersonnelID FROM qryPersonnelTrainingByAdHocPersonnel;
The where statement of potential use:
Code: WHERE (((tblPersonnel.PersonnelID)=[Forms]![frmMain]![lbxPersonnel]));
Can anyone help me with how I can accomplish this?
Here is what I currently have:
10 SQL Pass-through queries to update different Date field (Date1, Date2, Date3, etc.) based on the value of the next date field.
This is how each individual query is set up (there are 10 in total) UPDATE tblApplications SET tblApplications.LastContact = [date1] WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
Here is what I would like to have: (as 1 query)
UPDATE tblApplications SET tblApplications.LastContact = [date1] WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
UPDATE tblApplications SET tblApplications.LastContact = [date2] WHERE (((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
UPDATE tblApplications SET tblApplications.LastContact = [date3] WHERE (((tblApplications.[Date Completed]) Is Null) AND ((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Not Null) AND ((tblApplications.Date4) Is Null));
Can I somehow combine them together like this or do I have to keep them as 10 seperate queries?
I understand that I can't set multiple "values" in a crosstab query but I need to have both a UPC and a price display in a report(Price List) for Our Exotic Wood selections like so:
I have 2 crosstab queries one that gets the price and one that gets the UPC I can join them and get it to print on every other line but there is no way that i can find to print two lines at a time in a report so my question is there a way to achieve the outcome described above with two crosstab queries? do I need more queries or a different kind of query?
I want to combine six different memo fields into one. I found this code and it works to combine two fields so I edited to add a third and it does not do anything.
I have a database that reports activities by region.
Each week, my regional volunteers report statistics on a number of club activities. This is in the form of zero to theoretically infinite activity reports that they enter on a website. I download the .csv from the website, add the activity reports to the activity table and send them a totals summary every now and then.
The summary report shows figures for every club in the region, even if no activity reports have been entered for that club that week or ever.
This works fine, including forcing the query to return zeros when no reports have been submitted for that club.
What I want to do is have the report also show (in brackets next to each figure) the position as it was X number of days previously.
I can make the query and report to show the figures now.
I can make the query and report to show the figures X days ago.
What I cannot work out is how to combine the two queries into one report source so that I can get
Club 1 100(50) 75(0) 45(45) Club 2 0(0) 0(0) 0(0) Club 3 20(19) 0(0) 200(50)
etc
If I try and make a third query that gets the sums from qryNow and the sums from qryXdaysago for each record in qryClubsByRegion, I get two lines for each club.
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.
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 intially, I wish to have attendance stats for 3 groups of people over 2 days in a report
i have the two queries for the raw data, but wish to combine into a report that will ultimately become a "dashboard" report for half a dozen queries., and can only get one or the other to work do i need to combine the queries into a summary query of sorts?
