SQL help needed

[Anja]

Hello everyone,

I have an urgent need for a report and I cannot figure out the SQL to
give me what I want here.

I have a table called Records_T which has the following definition:

ID - Autogenerated number
Amount - A currency value
SupplierID - references an ID in the Supplier table.

The supplier table is also very simple:

ID - Autogenerated number. This is referenced in the previous table.
Name - A name string

Now, what I want to do is get the sum of the total amount for each
supplier.

So, say we have the supplier table with the value:

ID Name
1 Amazon

Now say the Records_T table has the following entries:

ID Amount SupplierID
1 100 1
2 100 1

So, I want to write a query or report that will show:

Amazon: £200

So, this gives me a total for each supplier....

How can this be done in Access?

Thanks for any help you can give me. Really appreciate it.

Anna

 

[Rick Brandt]

Hello everyone,

I have an urgent need for a report and I cannot figure out the SQL to
give me what I want here.

I have a table called Records_T which has the following definition:

ID - Autogenerated number
Amount - A currency value
SupplierID - references an ID in the Supplier table.

The supplier table is also very simple:

ID - Autogenerated number. This is referenced in the previous table.
Name - A name string

Now, what I want to do is get the sum of the total amount for each
supplier.

So, say we have the supplier table with the value:

ID Name
1 Amazon

Now say the Records_T table has the following entries:

ID Amount SupplierID
1 100 1
2 100 1

So, I want to write a query or report that will show:

Amazon: £200

So, this gives me a total for each supplier....

How can this be done in Access?

Thanks for any help you can give me. Really appreciate it.

Anna

In a new query design add both tables joined on SupplierID. Pull the
Supplier name and the Amount fields down into the grid. In the main menu
enable View - Totals. Set the Totals row under Supplier Name to "Group By"
and under Amount set it to "Sum".

BTW, "Name" is a reserved word in Access. You should change that field name
to SupplierName.

 

[Anja]

In a new query design add both tables joined on SupplierID. Pull the
Supplier name and the Amount fields down into the grid. In the main menu
enable View - Totals. Set the Totals row under Supplier Name to "Group By"
and under Amount set it to "Sum".

BTW, "Name" is a reserved word in Access. You should change that field name
to SupplierName.

You are a legend!

Many thanks