Query design question (counts, sum, and other fun stuff)

[CRAMSEY13]

For my employer, I have a project to import two different reports into two different tables. Then, there is a general sales code table that contains a list of sales codes. Each sales code represents a branch of the armed forces and other trading partners of the government. The first report that is imported into tbl137Report. This table has field names of SponsorNumber (this identifies who the "customer" is), Dept, Appr, Subhead, and the accounts receivable total for the sponsor. The second report will be imported into tbl865Report, and the data fields will contain SponsorNumber, BillNumber, BillDate, Amount.

Query 1
SponsorNumber BillCount (This would be a count of the number of bills for each SponsorNumber...for example, in tbl865Report discussed above it may have three bills for three different amounts...let me put an example of that table and then the results that I need from the query.

tbl865Report
SponsorNumber BillNumber BillDate BillAmount
SponsorA BillA 12/30/16 150.00
SponsorA. BillB. 12/30/16. 200.00


SponsorA. BillC. 12/30/16. 100.00

Desired Output after query
SponsorNumber. BillCount. Amount
SponsorA 3. 450.00


Query 2
I need to somehow update tbl137Report with the information in Query 1.

Add a table column for the BillCount from Query 1 and update the amount column with the amount from our Query that consolidated the data.

How would I accomplish this? I have basic familiarity with using modules, so it anybody has code I can put in there it would work.

I would like my final result to be something like
SponsorNumber Department Appropriation Subhead BillCount from Query1 Amount updated to reflect the amount in Query 1. I hope this makes sense. I am really struggling with this work project.

[ssanfu]

Query1

SELECT tbl865Report.SponsorNumber, Count(tbl865Report.BillNumber) AS CountOfBillNumber, Sum(tbl865Report.BillAmount) AS SumOfBillAmount
FROM tbl865Report
GROUP BY tbl865Report.SponsorNumber;

Query2

INSERT INTO tbl137Report ( SponsorNumber, BillNumber, BillDate, BillAmount )
SELECT Query1.SponsorNumber, Query1.CountOfBillNumber, Query1.BillDate, Query1.SumOfBillAmount
FROM Query1;

What happens if there is a 4TH entry in tbl865Report with a different date?

SponsorNumber	BillNumber	BillDate	BillAmount
SponsorA	BillA	12/30/16	150.00
SponsorA	BillB	12/30/16	200.00
SponsorA	BillC	12/30/16	100.00
SponsorA	BillD	12/31/16	175.00

Does the bill count become 4 and the total becomes 625.00?