Query Weekly Totals

Aug 15, 2007

On the attached jpg(s) you will see the query I'm working with. It's fairly simple except for a couple of things and those are the items I'm stuck on.

What I'm trying to do is get a representation of the amount of traffic that has come through our salescenters in the last week. Some centers had no traffic and others had multiple prospects. So when I look at the query for week 33 (this week) I would like to see all the centers even if they had zero traffic - the query should show zero.

If you look at Traffic_Query.jpg you'll see how the table is laid out. With the query I'm trying to show all of our centers for the current week even if no traffic was entered.

I may not be explaining it clearly so if you have any questions let me know.

Thanks,
Chester Campbell
Joseph Freed and Associates

View Replies

Creating Weekly Report From Cumulative Totals

Mar 13, 2013

I have an excel report which I would like to run through Access to drive trend analysis and compare with other similar reports. The excel report has a cumulative spend figure each week and not the actual weekly spend numbers, the budget figure also can change depending on the actuals.

Excel report:

Week 1
Product ID
Customer
Yearly Budget
Spend

1122
Sam
100
3

1123
John
200
4

[code]...

Will I need to create a new table each week or can I link the file and it updates automatically?Can Access store the weekly data and just update it one week at a time?

View 3 Replies View Related

Totals Query/sums/grand Totals

Sep 4, 2007

Hey all! This is my first post. Been searching through the net all day trying to find a solution to this problem. Basically i have a table that looks like this (regular text is what i have and bolded text is what I need:Name Date Qty MOBrad 12/12/2007 23323 4423John 12/11/2007 3445 4432 John 12/11/2007 344 4432 John 12/11/2007 45 4432 John 12/11/2007 44 4432 John 12/11/2007 3445 4432 Grand Total: (Qty)And then I'd like to be able to carry this over and display a grand total at the bottom of every page of a report that I would need to generate. Our company produces forms and we sometimes have 60 - 70 people working on a single job. We want to see their hours individually but we would also like to see a grand sum of all their hours. If someone could help with this or needs more info let me know. Thanks for all your help!

View 14 Replies View Related

Weekly Query

Mar 18, 2006

hi,

I am creating a booking system and I need to create a query for a report that will show how many bookings are made per week and the total of the bookings. I have managed to make a query that will get the number of bookings and the total, but i have no idea on how to do this for each of the weeks in a month. From this query i hope to then make a report showing 4 weeks at a time.

Any help would be great. :)

View 3 Replies View Related

Queries :: Weekly Query That Update Reports

May 16, 2014

I have made a query that was supposed to update my reports every week with new inquiries that we get.

I thought i had solved this but when i opened the report this morning, it's just showing last week's.

I have this as the criteria:Between (Date()-7-Weekday(Date(),2)+1) And (Date()-Weekday(Date(),2)+1)

It's probably wrong.

View 7 Replies View Related

Use A Query To Do A Running Total & Comparison With Weekly Data Points

Apr 13, 2006

I am a newbie, so please forgive me for such an easy question, but I am stumped. I attached a text file that shows the data I am working with. What I need to do is take each product (labeled Prod) and do a weekly sum on the quantities and compare against a set number to see if the quantity is lower or higher. For instance, I need to take column 12, regardless of value and compare it against set number. If the quantity is less, then I need to add the value of column 12 to column 13 and compare the summed value against set number. Again, if the sum is less than set number, I then need to take the value of column 14 and add it to the summed value of the previous step (sum of 12 & 13), then compare this new sum to set number. This process keeps taking place until I reach a summed value that is greater than set number. Once that happens I need to identify the column that sent me over the set value and hold that data. For instance, if column 33's (out of 52) summed value takes me over the set number, I want to know that it was column 33, so I can run further calculations against that value. The column header's are week numbers and I need to identify order points based on lead times and when I will run out of material. Is this beyond queries? I think so, but if it is, I don't know how to exactly begin the code in VBA either. I think I would use an If then Else stucture with a counter switch set from 1 to 52, unless comparison exits function, but not certain. HELP?????:confused:

View 3 Replies View Related

Queries :: Create A Query That Can Run On Weekly Basis And Save Results To Excel Spreadsheet

Feb 18, 2014

I have a database of around 15,000 users and I'd like to create a query that I can run on a weekly basis and save the results to an Excel spreadsheet. The results need to be logical and understandable by my coworkers.

Unfortunately, the actual results of the query are not (in their raw form) logical or easy to interpret.

Let's say I have a table called "users" and within that I have:

Surname
Forename
FieldA
FieldB
FieldC

FieldA has a value of either NULL or a 12-digit number
FieldB has the values are "ENABLED", "DISABLED" and "N/A"
FieldC contains a value of either "1" or NULL

This means nothing to my coworkers who want each user to be sorted into a "category". As I'm running this on a weekly basis, I'd like this query to do the work for me, so I don't have to manually assign everyone to a category in Excel. Plus, of course, there is no chance of human error if the query does this for me.

Sooo... I'd like my query to categorise for me as follows:

Category1 = FieldA IS NOT NULL and FieldB="ENABLED"
Category2 = FieldA IS NOT NULL and FieldB="N/A"
Category3 = FieldA IS NULL and FieldB="ENABLED"
Category4 = FieldA IS NULL and FieldB="N/A"
Category5 = FieldA IS NOT NULL and FieldC = 1
... etc.

I'd like the final column in the query results to simply list the category name, so I can simply copy and paste the data into an Excel spreadsheet and be done with it, safe in the knowledge that it makes sense to all.

View 14 Replies View Related

Queries :: Create Parameter Query To Select Which Column To Calculate Weekly License Info

Mar 2, 2014

Basically, I have a database table that is maintained every week. It is about e-mail account licenses for Office 365.

Each column represents a week of license data for every mailbox account which is about 10 000 plus users with the date as the field headers( something like "License information as of 06122013").

Basically, I have created 52 queries based on the license type I require to be calculated & I have a form created to control it. However, right now it is all output only as I have yet to create any user parameter to specify on any of the queries about the date I want

As I am currently unsure how to specify all the queries to use one field date header which I want the user to specify via drop down list in order to calculate all the licenses on this specific date.

For example, if I want to see the license information for 06/11/2013, I would choose "License as of 06112013" & it would then run all the queries based on the header specified earlier & output that information on the form.

Right now the queries are all configured as the "Select" type.

View 1 Replies View Related

Totals In A Query

Jun 23, 2005

I have a table. The table stores a workers name and information such as Primary Station Verified.
The Primary Station Verified is a yes/no checkbox. It also has the dates that each worker is verified.

I want to do a query that will show me the total number of people and out of that total number of
people how many are verified. Then I will turn that data into a graph.

How do I do the Query to show me (example) There are 50 people and (example) 25 of them are
verified? I know how to do the query to show all of the people and who are verified but I just want
the numbers.

View 1 Replies View Related

Totals Query

Jul 6, 2005

I'm needing help with a totals query. I have a table with a Department field, a charge field, and a charge code field.

I'm running a query that first groups by the department, then uses the max function to give me the highest charge within that department. For the third field of the query, I want to return the charge code that aligns with the highest charge, unfortunately none of the general totals seem to work and I can't figure out an expression to work either

View 1 Replies View Related

Totals Query

Aug 21, 2006

Hi,

Im very new to MS Access so bear with me.

I have a table containing travel claims data. In some instances there are multiple claims attached to certain policy numbers. Currently the Table has a seperate line of data for each and every claim. What I need to do is to ensure that there is only ever one line of claims data per policy number and that columns containing data such as: Total Reserves / Total Paid / Net Reserves / Claims fees are totalled on that line.

The reason I need to do this is that I then want to run a query that compares total claims per policy against total premium per policy.

I would be grateful for any help.

Regards

Matt

View 6 Replies View Related

Totals Query

Dec 4, 2006

I have a table that I want to sort by the by class year. But i have like 20 options that are yes/no that i want to count the total of each so that i can do charting in excel.I have no idea how to create the query. I know that I only have to count the yes's.Bascially I need to my query to show me something like thisYear AE ALD TUTOR FS2006 3 5 8 12007 5 5 9 0Year is a numberAE, ALD, Tutor, FS are boolean yes/noi don't want the select to find what is true for all just count each field individually but display it together.Does that make sense?I am really lost please help.

View 1 Replies View Related

Query Totals

Feb 7, 2008

I need a query to show me totals, but I just need the totals on one line and not an entire column of totals. Any suggestions?

View 1 Replies View Related

Running Totals Query Help

Jun 23, 2005

Hi - Once again I'm asking for your assistance. Ideally, below is how the form (and eventually a report) would look; the first three fields (ID, Date & Vol) I currently have in a table (tblMaster):

ID Date Volume YTDTotal Q1Total CurrQTDTotal Q2Total
A1 Jun-05 10,000 77,000 40,000 37,000 37,000
A1 May-05 15,000 67,000 40,000 27,000
A1 Apr-05 12,000 52,000 40,000 12,000
A1 ------ ------ 40,000 40,000

(Sorry, I couldn't get the above data to line up right :confused: )

How would I build a query such that I could show the above in a form/subform (and eventually a report)? Help is greatly appreciated.

View 1 Replies View Related

Totals Query Problem

Jun 24, 2005

I want to do something fairly simple. I have 2 tables that are structured as follows:

Table_FormLogInfo (Table #1)
-------------------------------------
ID
AreaID
FormReceived
ReceivedDate

* Note that the field "FormReceived" can be set to 1 or 0.

Table_AreaAbbr (Table #2)
---------------------------------
AreaID
AreaAbbr

I'm trying to set up a totals query where I do the following:

AreaAbbr is in column 1 with Total set to "Group By"
FormReceived is in column 2 with Total set to "Count"
FormReceived is in column 3 with Total set to "Where" and Criteria set to 1

The query is working correctly but not exactly how I want it to. It returns only those "AreaAbbrs" where the "FormReceived" field is set to 1 and it counts them. What I would like it to do is return ALL "AreaAbbrs" and set the count of "FormReceived" to zero instead of omitting those that do not have "FormReceived" set to 1.

Can I accomplish this?

Thank You,

Becky McDermott

View 6 Replies View Related

Overall Totals In A Crosstab Query

Aug 26, 2005

A crosstab query gives the totals for students in particular classes per the Class they belong to, i e

College A � Class1 4, Class2 12, Class3 17
College B - Class1 12, Class2 124, Class3 12
College C - Class1 5, Class2 16, Class3 7
College D - Class1 4, Class2 13, Class3 9
Etc etc

Experts, is there a way to an overall total of the lot, ie

Class 1 30, Class 2 48, Class 3 28, Class 4 etc etc

please?

View 2 Replies View Related

How To Make A Row With Totals In A Query

Sep 12, 2005

I have to make a query using information out of an existing table. In the table there are two columns which I have to use in the query. I need to have the totals of these two columns. The table looks like this:
country amount1 amount2
A 2 3
B 4 6
C 5 2
D 5 3

What I want the query to look like is:
country amount1 amount2
A 2 3
B 4 6
C 5 2
D 5 3
16 14

As I am dutch, my english will not be perfect
Thank you in advance

View 6 Replies View Related

Summing Totals In A Query

Sep 22, 2005

SELECT Commissions.TransactionID, Commissions.OriginatorID, Commissions.AmountPaid, Commissions.DatePaid
FROM Commissions
WHERE ((Commissions.DatePaid)> DateAdd("d", -32, Date()));

I want to add up commissions paid in a month using a simple query and im not sure how to proceed from here. This example will pull records for the last 32 days, but now how do I do the sum commissions.amountpaid to work?

Thanks for any help.

Scott

View 5 Replies View Related

Totals Query For Groupings

Jan 10, 2006

I'm looking for suggestions on the best way to do this...

I want to track our total sales within a date range but by certain groupings of categories. This is for disc jockey bookings. We have a field named "Job_Type" and we would like to query our sales based on groupings...

Weddings (Wedding Reception, Wedding Ceremony, Post Reception, Coin Shower)
Corporate (Holiday Party, Company Picnic, Company Party, Convention)
School (Prom, Homecoming, School Dance, Graduation)
Other (everything else)

I have already set up 4 separate queries, one for each grouping, with a union query, and it works but it returns 4 rows and doesn't specify which row pertains to which query.

Can anyone suggest an alternate/easier way to do this or a fix for the union query problem?

Thanks!

View 2 Replies View Related

Inventory Totals (sum Query)

Mar 9, 2006

I have an inventory table with the following feilds

InventoryTransactionID
ProductID
UnitsReceived
UnitsSold
UnitsShrinkage

I would like to create a query to give me an inventory for each product that i can reference to in a UnitsInStock control in a Product form.

I have tried the following with no success. What am i doing wrong?

UnitsInStock : =Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))

I get this error "You tried to execute a query that does not include the specified expression <name> as part of an aggregate function." (Error 3122)

Thanks for any help you can give me.

View 1 Replies View Related

Totals Count Query ?

Mar 10, 2007

I have just got my head around a aggregate query to count the number of results of a certain date using the search here on the forum but . .

I was trying to count all dates by the day of the week?

There are over 1000 dates but i was hoping for a query with 7 results showing how many on each day i.e. mon 300, tues 250 etc etc.

Is this possible?

View 1 Replies View Related

Can I Add Totals To A Query Column?

Jul 10, 2007

I have a rather simple query that shows the number of layoffs in a given period and would like to know if I can have the query sum the numbers.

Company City Date Incident Laid-Off
CompanyABC CityABC 5/3/2007 Mass Layoff 207
CompanyKLM CityKLM 5/5/2007 Mass Layoff 54
CompanyQRS CityQRS 5/7/2007 Plant Closing 436
CompanyXYZ CityXYZ 5/9/2007 Plant Closing 75

Is it possible to somehow display the total (772) underneath the "Laid-Off" column? Or create a separate column that only displays the "Laid-Off" total?

Or would it be easier to just build a report from the query and display laid off totals on the report?

(I apologize for the "table." I tried to import the query results as an image but couldn't get it to work.)

Thanks in advance for your help,
Kerry

View 3 Replies View Related

Totals Query To Find %

Aug 9, 2007

I have a DB that has lots of queries, I'm trying to find the percentage of trainees that are employed from the number of trainees in my database. For testing purposes, I have three trainees from NCN and two of those trainees are employed.
Now, i made a query to find the total number of NCN-trainees employed which has three tables and seven fields (Fname, Lname, Group[NCN],Employee Status[full-time, part-time,un-employed],employer number, and employer). three records in total
The other query I made is to find the total number NCN-Trainees. This query is based on the same three tables but only has 4 fields (Fname, Lname, group[NCN], and employee status) two records in total
The trouble I'm have is which field to put the totals under.

Any suggestions??

View 1 Replies View Related

Totals / Count Query ???

May 2, 2008

Hi,

I have a table showing members of a club.

I need to show how many members use each particular mode of transport.

Dan

View 10 Replies View Related

Totals Query In Text Box

Aug 26, 2004

I am having problems with query results appearing in a text box. The query is filtered by two combo boxes on my form. The query works fine and gives me the correct results when I run it by itself and enter the required info.

These are the two control sources I have used for the text box (without the quotes, of course):
"=qtotRecords!CountOfLast_Name" and "[qtotRecords]![CountOfLast_Name]"

Maybe including my query will help. The two control boxes on my form are cboSchool_Name and cboSeminar. This is my query:

SELECT Count(tblMain.Last_Name) AS CountOfLast_Name
FROM tblMain
WHERE (((tblMain.School_Name)=[Forms]![frmAdd]![cboSchool_Name]) AND ((tblMain.Seminar)=[Forms]![frmAdd]![cboSeminar]));

All I get in the text box is #Name?

Thank you in advance for any assistance.

Best regards,
MrAviator

View 4 Replies View Related

Calculating Totals In Query?

Aug 22, 2012

I'm trying to build some queries with calculations in, and I'm not able to do what I had hoped. Am I doing it wrong?

This is about sales.I'd like to firstly, be able to have a quick query, to see how many items a seller listed, with total price (this seems fine, if I count the item IDs, and sum hammer price in a query).And vice versa, by buyers.But, I'd like to sum for sellers, number of items Sold, Returned or Relisted.

I have a status (blank for active, "sold, "relisted" or "returned", and thought, I could "count", by the status = "Sold" (ie), but it won't accept the expression.

for sellers, I had hoped to have one query, with total items listed, total money made, then totals for still active, sold, returned and relisted items.

Am sure I'm doing something basic wrong, but I can't think what.

View 2 Replies View Related