Group By Data From Different Tables Based On Two Sums

May 23, 2014

I have 2 tables and one query as below

1- Order Table

OrderID | CustomerID | CusromerName | OrdDate

2- Payment Table

OrderID | Amount | PYMDate

3- Order Deatails Query

OrderID | ProductName | Quantity | Price | Total ([Quantity]*[Price])

I want to group the orders based on the sum of total and sum of payments. I made another query as following :

OrderID (From order table) | Payment (from payments table) | Total (From Order Details Query )

At the Total field I set the above fields to

Group By, Sum

and Sum respectively

I got true payments sum, however, the sum total are always duplicated.

Note that the three objects (two tables and query ) have relation (type : one to many) by the order ID

View Replies


ADVERTISEMENT

Queries :: Create Sums Based On Weekly Range?

Jun 26, 2014

I'm creating a query for a someone who wants quantities summed by a weekly range and correspond to a week number. This person wants Access to do something that is a simple function on Excel.

I have no clue how to do this or if Access is even made to do something like this.

This is an example of what I have so far

Quote:

P/N QTYWork DateWW (Work Week)
25COMP16/11/201428
25COMP26/20/201425
25COMP27/9/201428
25COMP36/20/201425

....And so on

My code is

Code:
SELECT dbo_JBKLG.JKPRT AS P/N, dbo_JOB.JBQOR AS QTY, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4)) AS [Work Date], DatePart("ww",[Work Date]) AS [WW]
FROM dbo_JBKLG LEFT JOIN dbo_JOB ON dbo_JBKLG.JKJOB = dbo_JOB.JBJNO
GROUP BY dbo_JBKLG.JKPRT, dbo_JOB.JBQOR, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4))

There are more part numbers and the data from the other parts must be in the same format in the same query.

Quote:

P/N QTY WK WW

25COMP 15506/09-06/15WW24
25COMP 22706/16-06/22WW25
25COMP 16006/23-06/29WW26
25COMP 20006/30-07/06WW27

.....And so on

View 14 Replies View Related

IIf Statement Used For Sorting Data Based On Option Group

Jan 10, 2006

Hi all,

My problem is as follows:

I am trying to generate a report, which is based on a query, which references a form that has an option group containing four options.

I want to sort the data according which option group radio button has been selected (they the values of 1,2,3,4).

Originally there were only 2 options and the query said (in SQL view):

ORDER BY IIf(Forms!frm_08_reportsort!opt_sort_by=1,TBL_Acco untManager.Name,TBL_SiteContactDetails.SchoolName) ,

This works fine for 2 options, but I can't figure out what command i should use for more than 2 options :confused:

Any help would be much appreciated :)

View 3 Replies View Related

How To Group Fields And Corresponding Fields, While Calculating Sums?

Aug 4, 2005

I have two tables - Hours and Employees. Employees table has these fields:
EmpID, and First, and Last name.
Hours table has these fields:
EmpID, Hours, Funding Source, Nature of Work, Pay Period

I need to produce a report or a query that will display these results:
A column with a list of all the employees and all the Nature of Work categories going across with the sum of hours for each Nature of Work. I'm attaching an Excel spreadsheet that shows kind of what I need. Also, these reports need to be produced for each Funding Source and
Pay Period, so something like [Enter Funding Source] and [Enter Pay Period]

My question is that I don't know how to make a report that will just list a column with all the names and count the sum of hours for each Nature of Work.

Hope this is not too confusing, and any help would be greatly appreciated. thank you!

View 8 Replies View Related

Help On Sums - Dates This Time + Rewriting Of Data

May 30, 2006

The Database is coming along nicely now, but in need of guidice with this one:

On the main table of the contacts I have what you could call a date of 01/01/06 and a combo box next or below that that I select what risk levels to asign the user.

Each user would have a no of year review based on the risk level:
High = 1 Year
Med = 2 Years
Low = 3 Years.

There is another date box that is blank at the moment, what I am looking for query wise or another way is on change/update the risk level is to add the start start in this case 01/01/06 (if user is Med Risk - then add 2 years) and adding risk level giving me a new date of 01/01/08 in the blank date box (this option will make the main table update.

Can anyone point me in the right direction for this one?

Thanks

Mike

View 1 Replies View Related

Updating Data On 2 Tables Based On Criteria

Oct 8, 2004

Ok. Here's the situation. I have query setup. This query has 2 columns:
1. Agent Name
2. Sale Value

I have 2 tables.
Table1: Includes Sales for this pay period only
Table2: Includes all Sales From past to present.

We are setting up our system on Tiers and Each sale value is going to change. So, Here's what I need:
I need to run an update query that first Looks up all the records in Table1 based on Agent Name. The field that needs to be updated is called SaleValue. It needs to be updated with what's in my Query mentioned above.
Then, all the values in Table1, need to be copied (or updated) into Table2. This can be done based on the Primary Key (SaleID), which exists in both tables. So I guess we will need to run 2 separate update queries. Any ideas on how this can be done?

View 8 Replies View Related

Pulling Data From Other Tables Based On Values Entered

Dec 13, 2007

I'm attempting to build a simple database for a martial arts tournament registration. The weight division (Fin, Fly, Heavy, etc.) depends on the age, the sex and the weight. I've tried this several different ways, most recently by using one table called "competitors" with all the entrant's information, and several other tables based on the age class/sex (i.e. I have a table called Age 8-9 Male with the weights and divisions in it, and other tables for the other ages/sex).
So, basically, what I'd like to have happen is a report that will lookup in the correct table the division, based on the weight entered. In other words, for a record where the age is 8-9 and the sex is Male, the report compares the weight to the values in the 8-9 Male table, or if the record is a 12-13 Female, it looks in the 12-13 Female table for the weight, and places the correct division in the Division field of the report.
Probably going about this wrong.....but any help would be appreciated.

View 5 Replies View Related

Tables :: Repaint BackColor Based On Data Entered

Sep 25, 2014

I am trying to color code text boxes based on the data entered in. The column is "PHASE" and the items entered in are either "1" "2" or "3". 1 is supposed to be Red, 2 Yellow, 3 Green.

I have Microsoft Access XP (I'm sure). I've tried to do it on VB and had no luck. I couldn't figure out Macro and I don't have On Click or Code Builder.

I have tried a couple of different expressions and no luck. I'm definitely an amateur to Access. I'm only using it cause it's what we have in the office.

View 8 Replies View Related

Queries :: Differentiate A Query Based On All Group Records Or A Query Based On Only One Record

Dec 22, 2014

I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?

View 14 Replies View Related

Tables :: Limit Data Input Based On Another Fields Criteria?

Dec 22, 2014

I have two tables linked by ID (Table1,Table2)

The Table1 holds data that is a request for a task.

ID, Request, Task_tms (the number of times the task is required to be done)

1, Make a cup of coffee,15

Table2 is my allocation of people to the tasks.

ID, Person, Task_done (the number of times this person has done the task)

1,Sally,10
1,Eddie,5

What I want to do is to limit the amounts of allocation in some way (ie in this case if I have already put 10 for Sally then I would be unable to put anymore than 5 when entering the amount for Eddie.

Additionally if I were to allocate all 15 to Sally then no additional people would be able to be allocated to this task (ID 1)

The two table are in datasheet format linked on a form.

View 1 Replies View Related

Tables :: How To Keep Data From Importing Duplicate Records Based On One Field

Sep 11, 2012

I'm able to import new data from excel just fine, but I can't import updated data from excel due to duplicates not being allowed for a particular field. Is there a way to keep from importing duplicate records based on one field, but still import data from other fields where the information is different from the excel file?

View 5 Replies View Related

Queries :: Top N For Each Combination Based On Group Averages

Jul 16, 2014

I need to create a query where in the end, I will have four rows of data based on based on two combinations of WaterSourceType and Crop.

I need the query to bring back the results of the average Top N (lets say Top 10%) for each combination.

I have tried this every which way and I can't seem to get it grouped like I want it. I NEED to have four distinct rows with the average of the ProfitPerBushel for each grouping.

Basically, what this does is show me the average profitablity of the top 10% in each grouping.

WaterSourceType | Crop | ProfitPerBushel

Irrigated | Soybeans | ProfitPerBushel
Non-Irrigated | Soybeans | ProfitPerBushel
Irrigated | Corn | ProfitPerBushel
Non-Irrigated | Corn | ProfitPerBushel

View 9 Replies View Related

Reports :: Control If Group Visible Based On Page

Feb 23, 2014

If it possible to change if a group is visible based on what page a different group is printed on.

The report I'm working on is a Invoice and it has subtotals in one group and totals in another group.

If the totals are going to print on page 1 then there is no need to print the subtotals. Is there a way to know what page the totals will print on so I can set visible for subtotal group to true or false?

View 6 Replies View Related

Queries :: Option Group - Query Based On Classifications

May 25, 2015

I used an option group to classify a record in my database. It works great for this purpose, but I am at the point now of creating a query, and I would like to query based on these classifications (or options). Is this possible to do?

Also, for the option buttons, the value is numeric. When I create the Query (or a report off of that query) I would like the actual text values to display, not the data value Access is using.

View 2 Replies View Related

Reports :: Pull Information Based On Group Field?

Jul 21, 2014

My report is grouped by a Billing Group #...within each billing group there are sub invoice numbers that are tied to the main billing group # (they all have the same billing group # but different general invoice numbers). I need to pull data if one of the general invoice # invoice dates is greater than or equal to a specific date entered.

In other words: If a Billing Group # and one of the Invoice Dates is greater than or equal to a specific date entered then pull ALL GROUP data to report.

View 14 Replies View Related

Reports :: Sort Report Based On Option Group On Form

Oct 9, 2014

I have created a form with an option group with 4 options (date, line, description and observation). Each of these options are to be a sort order for a report that will open after the user selects an option and clicks a button. I am trying to code the button using a select case so that case 1 opens the report sorted by date, case 2 by line, etc. how to write the code for the sorting.

View 6 Replies View Related

Forms :: Repeat Record Entry Based On Option Group Selection

Sep 10, 2014

Is there a way to duplicate, triplicate, a record based on an option group selection. i.e.

Form
Field: Option group
Value = 1
Value = 2
Value = 3
Field 2:
Field 3:
Field 4:
and so on.

Basically if value 1 is selected, then I fill out the rest of the fields hit enter and go to a new record. But if value 2 or 3 is selected, I fill out the the fields, hit enter. The information is recorded said number of times based on the selection in the option group. Ideally I would have2 or 3 of the same record in the db, with different Primary keys of course.

View 3 Replies View Related

Tables :: Primary Web Based Inventory System - Update Raw Data From Another System

Mar 13, 2014

I have our primary web based inventory system that I am exporting to Excel and using this as an import to Access for the main raw data for my database. This being inventory it changes daily so I am updating this table every day. When I try to append the table it ads all the records. I am wanting an easy way to add only the new records/take out the ones that are no longer there. Basically update the table with what is currently there.The only have I have found to do this is by running non-matching queries and update queries.

View 2 Replies View Related

Tables :: Set Up Validation Of Fields Unit And Size Based On Another Fields Data?

Nov 18, 2013

Basically in my order details table i have the following fields

Product
Unit
Size

At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie

ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big

When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.

How do i set up validation of the fields Unit and size based on another fields data?

View 4 Replies View Related

Joining Tables, Group By Name, Select On...

Nov 18, 2006

Hi I have 2 tables(tblA and tblB) and I want a query result(tblC) where ‘group by’ only one record per individual, with the total added together and the type starts with ‘01’. Below(tblA and tblB) are examples of the two table. TblC is the query result that I want. tblAName---Type---Amount---StatusJoe---01FAE---$10---NewJoe---01FBE---$10---NewLee---01FBE---$10---OldBob---30FBE---$30---NewtblBName---Type----Amount---StatusJoe----01FBE---$5------NewLee----01NAE---$20-------OldLee----30FBE--$30------NewtblCName---Type---AmountJoe---01FAE---$25---NewLee---01FBE--$30---OldI have tried linking/joining, adding SUM function, First, Group By, etc. but all the results came out to be all different. Also, am I support to add a ‘join’ of tblA to tblB on BOTH the NAME and TYPE? I am so confused! Please help.Thank you very much. Joe

View 1 Replies View Related

Sums In A Query

Oct 13, 2005

I have a query which shows employee Totals it has the following fields:

Emp: [EmployeeLastName] & ", " & [EmployeeFirstName]

LogHours
Total: Sum

TotalWorkedPgs
Total: Sum

WorkDays: Sum([LogHours])/8

AvgPgs: (Sum([TotalWorkedPgs]))/(Sum([LogHours])/8)

JobType

JobEffort

-----

Job Effort is a has 3 values: My Corrections, Others, YOYO

I would like to know the:
Sum of TotalWorkedPgs when Job Effort is My Corrections
Sum of TotalWorkedPgs when Job Effort is Others
Sum of TotalWorkedPgs when Job Effort is YOYO

Because I need all of this information on a report.

Any help will be appreciated.

Thanks

View 5 Replies View Related

Counts And Sums

May 6, 2006

I've been trying to use a sum in a query to add up the currency of several records in a field from another table but as yet have had no success. The sum simply shows all the records but on there own and not in one total :confused:

I also need to use a count to total up the number of records in a table with a certain piece of data, in this case a 1, but again it does the same as the totals and simply shows each record on its own

If anyone knows what I am doing wrong I would greatly appreciate some help and advice

Thankyou

View 4 Replies View Related

Sums And Null Value Help Please

Jul 13, 2007

This has got to be really simple but I just cant figure it out because Im a novice.

I have a dbase based on clients and each client has a subform for where you can enter payments owed and payments received.

The queries run smooth when there is data but if there are no records for the payments owed or payments received, I get an nothing and #errors when I try and manipulate the data.

Im guessing this is a null value error and Ive tried the IsNull and Nz fuctions but I cant get them to work for me.

Should these functions be placed in the query which calculates the sum?
If so, how?

For example, my query is

Field:Hearingwcb
Table:HearingShowQuery
Total:Group By
Criteria [Contacts]![wcb]

This extract the appropriate records for the current client

Field: HearingFee
Table: HearingShowQuery
Total: Sum

This sums them up.

Im guessing because the sum is null, I am getting nothing. How can I get this to return a 0. I think that should take care of my problem.

Thanks so much...its driving me crazy!

View 2 Replies View Related

Invoicing Sums

May 26, 2005

I have a report that is broken up by invoice number. I want to create a text box that will sum the lines for each invoice number. I have the text box set to add the lines, but currently it is grabbing the first value for each multiple line invoice. Confusing, I know. See below:

Invoice number Date Amount
123456789 5/23/05 23,100.00
123456789 5/23/05 1,000.00
Amount: 23,100.00 <--- I can get this to = the first line for each invoice number but not total (should = 24,100.00)

Invoice number Date Amount
987654321 5/24/05 525.69
Amount: 525.69


Can anyone help me?

View 8 Replies View Related

Negative Sums

Jul 17, 2007

I'll see if I can make this clear and concise.

I basically want to sum totals(or rather subtract totals) from groups in a report. The problem is that all my numbers are positive and need a way if possible to change some to negatives.

I have 3 categories set up e.g

Account Payable , Accounts Receivable and Non-Revenue

In the form used for input , there is a drop down box in which you select 1 of the three account types. There is second box in which you input the amount (always positive). This second box is common to all account types therefore no matter the account you input the dollar values into this second box.

Right now the report is summing all values in the group so that it's not subtracting account payable amounts from account receivable amounts...Any help would be appreciated, and hopefully it will be a simple solution. NOTE: i would like to stay away from writing or editing any code. Thanks in advance

View 3 Replies View Related

Group Data In Qry

Feb 28, 2008

Hi, in the qry attached I would like to group the records by yyyy mm int the field "Year month" I need then to count the number of records in HOTAC which have value "yes" the sum of "Total Flight Time" and finally the count of records that contain the letter F or P in the "Duty" field.
Example:

Year Month Hotac Flight Time Duty
2007 05 0 2.18 2
2007 06 8 XX 2

How can I get that?

View 3 Replies View Related







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