calculation with parameter

[k11ngy]

Still having trouble with this one. I have query with paremeter, "Choose
Round" and the final report is based on this paremeter query. The report has
a total for Customer in this report for that particular round. The same
customer is also on a different round with another total and I want the 2
totals to appear as ONE on whatever round is requested. Paperwork saving
exercise for driver rather than having to refer to different rounds to get
total due from customer.

Any help appreciated

Regards

Steve

 

[BruceM]

You will need to post some details of the database structure. Essentially
you will probably need to use a query that looks for all instances for a
customer, perhaps grouped by round (whatever that is), but as I said,
details are needed.

 

[k11ngy]

Database details

Main table contains

Customer name

Address 1- Street Name
Address 2-House Number
Round – IE Monday 1
Current Due
Past Due
Total Due

Query based on Table (Parameter) will select say Monday 1 and the driver
will have a report based on this that shows him addresses on that particular
Round and more important what that address owes.

The same happens if he goes out on say Friday (Friday 1 round)

The problem is that an address can have a clean on Monday 1 and Friday 1 so
appears on both round sheets with a different total on each. Trying to work
system so that if the driver has either Monday 1 or Friday 1 round paperwork
with him it could show the totals added together

I can give him a list (Statement of account) with address grouped and he can
refer to that, just means more paperwork

Will battle on but if any ideas they would be appreciated

Regards

Steve

 

[daniel]

esta vez voy a irme de vacaciones me contrlais por favor todo lo que querais
acer todo el grupo atentamente DANIEL desde windows hotmail si podeis
controlarme dedesde alli me voy semana santa y vacaciones de verano

 

[BruceM]

Customer information should be in its own table. It should contain only
attributes of the customer, such as name, address, phone, etc. Account
information is not such an attribute, but rather related information. Same
goes for past due and current due.

It sounds as if there are delivery rounds or something of the sort, and a
customer may be included in several different rounds. The situation, then,
is one customer may be part of several rounds, and each round includes
several customers, so there is a many-to-many relationship between customers
and rounds. Also, each round consists of specific addresses. For this you
need a structure something like this:

tblCustomer (Customer Table)
CustomerID (primary key, or PK; could be autonumber)
CustomerName
Address, etc.

tblRound
RoundID (PK)
RoundDay
Other data specific to the round, but not the specific addresses

tblRoundDetails
DetailID (PK)
RoundID (link to tblRound)

tblCustomerRound (junction table)
CustomerID (link to tblCustomer)
RoundID (link to tblRound)

Build the relationships using the Relationship tool (View >> Relationships).

Use a form/subform to build the round data, storing it in tblRound and
tblRoundDetails.

Create a form based on tblCustomer (this is where you would enter customer
information) with a subform based on tblCustomerRound. The linking field
properties between the main form and the subform control is CustomerID. On
the subform, create a combo box bound to RoundID. Its Row Source is
tblRound, or a query based on tblRound.

You could do it the other was around, with a main form based on tblRound and
a subform based on tblCustomerRound. Reverse Customer and Round in the
above description to achieve this.

You also need a table for account information. At the least this would be
an Account table related to tblCustomer. More likely each item on the
account would contain several details, so you would need an Account Details
table related to the main Account table. With this structure you could see
all of the account information for a customer, regardless of when the rounds
take place.

This is just an outline. I cannot really take you through all of the
details. What you have described so far is a spreadsheet. Access is used
to build relational databases, which are based on very different way of
organizing the data. It may help to read about how databases are put
together. This is as good a place as any to start:
http://allenbrowne.com/casu-22.html

There are several links on the page that will lead to further information.