small problem with sums

[BenW]

well i created a relationship between 2 tables where:

tableA = invoice data
tableB = company IDs

i have 2 questions that i have been unable to resolve on my own.

1. i want to create a query where i am able to find all of the invoices from a single company using it's ID and add up all of the account payable/recieveables i learned how to add all of the values on a field but i don't know how to add the values on a specific row. so while i can add all of the ap/ar on the table with all of the information, i can't add the ap/ar of a single company despite the fact that my table sorts them apart (because i don't know how ><)

would a code like iff([company id]= "#",add) be close to what i'm looking for? because that's all i'm been fiddling with to try to get my query to do what i want.

so far i'm stuck with:

AR: IIf([company ID]=1,[AR$$],0) whereas 1 is the ID of the specific company i'm trying to pull records for and AR$$ is the field that i'm trying to pull the information from.

2. find a way so that i can get my table to print all of the records for a specific company based on the ID. so for example, if i wanted all of the invoices from company #9 then i would pull them out and print them. i tried expanding the + sign on the company ID table and printing it, but it obviously failed -.-"

i would really appreciate any help that can be provided to me! i just started access so anyone that can link me to an article related to this would be greatly appreciated because i don't know the terminology/keywords that i should be searching for in the article page that i'm using for this case.

 

[macca the hacke]

Just add up all the values on the field (as you have been doing), but also include the company ID in the query. If you want a specific company ID then put the parameters for this in this field

 

[BenW]

Just add up all the values on the field (as you have been doing), but also include the company ID in the query. If you want a specific company ID then put the parameters for this in this field

i tried doing what you said but i keep getting a message that says company Id is not part of an aggregate function. when i remove company Id from my table all of my AR are added together because i put in this code:

AR: Sum(ARAPtbl.[AR$$])

but the problem is that i already know how to do this and i'm not trying to add them ALL together but specific AR that are from a specific company based on the ID#. my code added all of the AR for company 1-10 but i just want company 1 for example but i don't know how to modify the code so i can get that specific company's sum.

edit:
actually, i don't want the sums of a specific company... what i really want to do is pull out the records of a specific company based on the ID# of the company so if i wanted to view ONLY the transactions of company #1 then would i be using the IIF() feature of access?

 

[Brianwarnock]

I'm having trouble understanding the problem but if I read it correctly then create a totals query by clicking on the sum / sigma sign on the tool bar change the AR field to sum and the Id tow here and put the criteria for the company in the criteria row

Don't see the problem for number 2 surely its a simple select query?

Brian

 

[BenW]

thanks for the help. i'm a bit confused on the criteria. how would i set this up? i want to seperate the sums of companies 1-13, but i'm not sure what to type into the criteria box.

EDIT:

i THINK i got it to work without using the criteria box. i added 2 tables into my query and simply put in the company ID from the CID table with the AP/AR table so now the numbers 1-13 are listed with the totals of each number. now i'm just trying to get the file to show me the single company. i can get all of 1-13 to show up but is it possible to get just company 1 to show?

 

[Brianwarnock]

I'm totally confused here Ben, to select company 1 put 1 in the criteria box, to sum for each company put nothing in the criteria box but change the where to GroupBy in the Total row, remember to select from the drop down list.

Brian

 

[BenW]

yea i was able to add the sums together but i was unable to use criteria so i simply created a mass of tables to fit my needs. a bit messy but it got the job done. now i'm just trying to find a way so that all of the numbers have their sum shown at the bottem. i'm trying to create a query that is similar to excel where each individual transaction is shown but at the bottem the values are all added up.

 

[Brianwarnock]

Ah! I think what you are after is a report showing all the details and then in the footer the sum.

brian