How do I: Sum of Selected Columns & Linking columns

[Riz-Man]

Hi,

Sorry if this is simple stuff but I've just started learning Access (using a book from the In Easy Step's series) and taking it step by step...at the moment.

Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.

In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients’ names and is selected from a drop down list that is linked to the Clients table.

What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?

Am I making sense? Is that possible? If so, how do I do it?

Secondly, how do I do a sum of selected columns for my “totals” column? Basically, I want to add the figures found in several cells that precede it?

Once again, sorry if this is simple stuff...but thank you for your time.

Regards.

Riz

 

[rsmonkey]

Rite lets get this straight... firstly lets deal with these totals cause thats nice n easy. First of create a query and add the neccesary tables hopefully your book has got you this far. If not start reading up on queries! Once you've added the appropriate columns you want to get a total of press the totals button it looks like this: ∑ you will see a new row being added to the Query design view which is a drop down box all of which are populated by the 'Group By' catergory. The values you want to add click this dropdown and select SUM. Then in a empty query column add this line but obviously manipulating it according to your table/field names: Overall: [Field]+[Field1]+[Field2]+[Field3]
Here i've added 4 fields together under the title Overall but you can add more or less fields your choice. When pressing the SQL button on my query you should see code like this:

SELECT tblA.Type, Sum(tblA.Field) AS SumOfField, Sum(tblA.Field1) AS SumOfField1, Sum(tblA.Field2) AS SumOfField2, Sum(tblA.Field3) AS SumOfField3, [Field]+[Field1]+[Field2]+[Field3] AS Overall FROM tblA GROUP BY tblA.Type, [Field]+[Field1]+[Field2]+[Field3];

tasty...

For your Combobox conundrum im not exactly sure what you are trying to achieve. I know you want to select a Client from a Combobox.. easy enough however You want some sort of information displayed in a text box next to it.. easy enough.. Is this information contained within the same table or diff tables.

Hope this helps and hopefully we can progress further wen u next respond! l8ers

 

[Riz-Man]

The sum of selected cells in different columns on the same row....

Thanks for your help.

Still working on the totalling bit but I'm not getting the desired results. Since I have not started reading up on queries yet, I'll need to look into this, as this may be why I've got it wrong.

However, please note that I made a mistake in stating exactly what I wanted. I want certain cells in a row of data (i.e. a record) to be totalled and not the cells from a column/field of data. So, for example, I want the figures in fields 1,2 and 3 of record A to be totalled in field 4 (on record A) for each record. The query I produced doesn't do this because I titled my post incorrectly and because I didn't realise the different terms used in Access (as opposed to Excel). It seems to be giving me a total of all the figures in a field of data (though it doesn't seem to be adding it up properly - but that could be because I don't understand queries yet).

I will work on this later and report back. In the meantime, if you could let me know what I need to do to get a total of selected cells in a record that would be great.

If I can't do this stuff...I won't even start on the other bit I needed help on!

Thanks again.

Riz

 

[rsmonkey]

Reet, what I said before is right I just need to explain but you really need to familirise yourself with the Query Design Grid to get a better Idea of what Im saying.

Ok Say we Have RECORD A and it has 7 fields FIELDS1 to 7. and you only want the totals of FIELD2, FIELD5, FIELD7. First we create a query and into the Query builder we add the table column that contains RECORD A for my example the Tables name is going to be: tblA and the Column name is Type. So add the Type field to the Query Design grid and where the row named Criteria is in the query builder type in RECORD A.
Ok so now the query shall only select records whose type match the context of "RECORD A". Now add the Fields which you would like to get a total of so in this case we want FIELD2, FIELD5, FIELD7. Now press the Totals Button this should add an extra row to your Query Design Grid. Now select 'SUM' under FIELD2, FIELD5 and FIELD7 in the Totals row. Finally in a new column of the design grid on the Field row put this: "Overall: [Field2]+[Field5]+[Field7]" This creates a new row in the query that is called Overall and simply adds up those named fields however you can pretty much use any aggregate function here within reason.
This should give you the desired effect:

Here's the SQL that statement will produce:

SELECT tblA.Type, Sum(tblA.Field2) AS SumOfField21, Sum(tblA.Field5) AS SumOfField5, Sum(tblA.Field7) AS SumOfField7, [Field2]+[Field5]+[Field7] AS Overall
FROM tblA
GROUP BY tblA.Type, [Field2]+[Field5]+[Field7]
HAVING (((tblA.Type)="RECORD A"));

Attached is an annotated screenshot to give some means to this madness ]
hope this helps

 

[Riz-Man]

The sum of selected cells in different columns on the same row....

Hi,

Thanks for your help.

I got the total query working though not quite the way you showed me but oh well. Thanks for your help.

Before, I move on to the next problem ;o) can you please confirm if it is possible to perform actual calculations in a table in Access (like you can do in Excel) without the use of a query.

E.g.: there are 5 fields in a table, and field 5 is the total of fields 2, 3 and 4.

Entering say 100 in field 2 and 50 in field 4 will result in field 5 showing 150 (automatically?).

Yes, as you can tell, I've only really ever used Excel so am coming at Access tables from an Excel view point which I know is getting me into trouble with getting to grips with Access.

Thank you for all your help to date.

Riz