Really hard SQL/Access question

[Bantam]

I'm such a bum, and if anyone can at least guide me to where I need to go to find out how to do this, or maybe an example I can fool with, it would be appreciated.

Oh boy. Here we go:

My company has 25+ salesmen throughout the US, and 8 or so sales supervisors over them.

I have a table (tblZip) with a record for every zip code in the US (100000 records, even though there aren't really that many), and each record has the zip as the primary key field (fldZip), a field for the initials of the salesman in charge of the zip code (fldCoordinator), and the initials of the supervisor in charge of the area (fldRFD). A salesman might be responsible for 300 zip codes or so, and a supervisor might be responsible for 1500, or thereabouts.

I also have a table (tblContacts) that keeps track of all customers' personal info (address, phone, etc, of which I'll need to know fldID, fldZIP, fldFirstName, and fldLastName), and yet another, seperate table for keeping track of the customer's activities (tblRegistrations) according to the user's ID number (registering for an upcoming service, in this case, and it also has fldID, fldDiscountCode). Finally, I have yet a fourth table (tblPaymentTransactions) that keeps track of payments that the customers have made toward what they've registered for, according to their ID (fldID again, and also fldAmountPaid).

I need to make several reports, from these four tables, on how well the several salesmen are doing out in the field. I basically want one report to look like this:

-------------------------------------------------------------------

Sales supervisor1 initials Number of customers

Salesman1 initials Number of customers
Salesman2 initials Number of customers
Salesman3 initials Number of customers

-------------------------------------------------------------------

Sales supervisor2 initials Number of customers

Salesman1 initials Number of customers
Salesman2 initials Number of customers
Salesman3 initials Number of customers
--------------------------------------------------------------------

This report will be for the top dogs in the company. The number of customers per salesman will be derived from the number of instances a range of zipcodes is hit inside the tblRegistrations table. The number of customers per supervisor is taken from the same thing, or can simply be a total of the individual salesman.


The next report will be for the individual Salesman:

Salesman1 initials
------------------------------------------------------------------
Name of Customer1 Amount paid
Name of Customer2 Amount paid
Name of Customer3 Amount paid
Name of Customer4 Amount paid
-------------------------------------------------------------------

The name of the customers will be taken from tblContacts according to their ID's


I know this is incredibly complicated, and I don't actually expect anyone to be able to help me. But at least I can hope.

If it helps, this can either be done in VB.net in an SQL format (and thus maybe manipulated in VB a little easier), or in Access (to form a report easier). Or if anyone has any other suggestions, I'm up for it.

Any help anyone can provide would be absolutely lovely. Yes, I am unworthy of the job position I hold, but I'm not paid well, and it's for a non-profit organization.

[Bantam]

Ten minutes after I posted this I kinda figured out how to do a report in Access. This stuff is too easy. Mmm, if anyone has any other suggestions, like on how to get totals of registrations instead of just listing their ID's, that would be appreciated...


-Jacob, stumbling about

[M Owen]

That can also be calc'd in a report ... If you have the data and a footer in a report ...

[Bantam]

Not really sure how to do that... is it in the wizard?

I'm such a loser...

[M Owen]

It's part of the report creation... When you design the report. If there is a numeric filed in the "Detail" section and any subsequent footer can be used for a summation point of that numeric field ... Play with it.

[Bantam]

Well I'm getting the report to work just fine, but I still can't figure out what I should do to count records of a certain type. Like each record ought to have a field that talks called 'Position Description' and at the end of each section I'd like a text box to display "Number of Supervisors", for example. So I'd have to have something that counts each record that has .Position = "Supervisor" or whatever. Anyone have any specific how-to on this kinda thing, or know about an online how-to for access reporting?

Thanks

[tridom]

The sql to count records is :

Select count(*) from {table} where {clause}

In Access, you can right click on the blue bar at the top of the query design box and select SQL View. You can insert a sql statement there and then return to design view.

If that helps any.