Calculations in Forms

[Guest]

Hi,
I have been working on creating a database for our ordered sales.
Originally I had created queries to determine the daily, montly and yearly
totals. I ran into a problem as I would need to create an update query so
that we can have daily reports and such. I have created in my form a daily
total, but am having some difficulty with how I should create the montly and
yearly totals. From what I understand about update queries it can't be done
when there are other queries dealing with calculations. Any help or a point
in the right direction would be greatly appreciated. Thanks.

 

[PC Datasheet]

First of all, are you sure you want to do this in a form and not a report?

In either case, you need a totals query to give you daily totals and a
crosstab query with calculated fields to display your data give you monthly
and yearly totals. Your form or report should look like:
Jan Feb Mar ............................. Dec Total For Year
1 D1
2 D2
3 D3
..
..
..
31D31
------
M1
Sum(M1 to M12)

D1 to D(last day of month) are daily totals. You need that for each month.
M1 is the monthly total. You need that for each month. Sum(M1 to M12) gives
you Total For Year.

 

[Guest]

Thanks for the information. I guess what I will do is leave the form for
just entering the order information. I will make the calculations in a
report as you suggested because we will be printing this information out and
it makes more sense. With the totals query, will I still be able to update
my database on a daily basis. I just got really confused with when an update
can and can't occur. Thanks again.

 

[Guest]

In looking at my databse again I have some other questions. First off at
this moment I have four tables.
accountexectutivetable: consists of acct.exe# and name of acct.exe.
customertable: consists of order date, company name, product amount and
account executive number.
sales month: idnumber and sales month name
sales year: idnumber and year

It was created this way when I was doing all the calculations through
regular queries. So now do I have to create a table for just days? so that
when I create the cross-tab query that information will be available. I had
to put this on the back burner and really haven't looked at the database in
about two or three weeks now, so now I am trying to get it going again. I
don't really know, but would it be easier to just start over from the
beginning? Thanks again for any help.

 

[John]

You don't need separate tables for the date information. You can use queries
to extract whatever information you need from the database and then you base
your form or report on the query. For instance, you can use the year or
month worksheet funtions to extract records grouped by year or month.
Open a new query in design view and add your customertable to the design
grid. In the first field of the first column type.
SalesByYear: Year([Customertable]![orderdate]).
In the second column type
SalesByMonth: Month([Customertable]![orderdate]).
Add the product amount field to the design grid and click the sum button on
the toolbar.
In the total row select group by for the year and month column and select
sum for product amount
John

 

[PC Datasheet]

Yes, it seems it would be easier to start over! Suggested tables would be:
TblAcctExec
AcctExecID
AcctExecNumber 'Do you really need this field?
AcctExecFName
AcctExecLName

TblCustomer
CustomerID
CustomerName

TblProduct
ProductID
ProductName
Price

TblSale
SaleID
DateOfSale
CustomerID
AcctExecID
ProductID
Quantity

This design of TblSale is for the sale of one product and any quantity. If
the sale consists of multiplr products, TblSale needs designed differently
and another TblSaleDetail is needed.

 

[Guest]

Morning, the suggested tables do look like that would work a little better
than the old ones I had. What I had done with the Account Exective tables
was not have an acct.executiveID and used their number as the identificaiton.
Each executive has specifice numbers assigned to them, which helps because
one executive has a number when working in a specific state, a different
number when working with the government and another number when working with
schools. Is it a bad idea to use that number as the id, or should I have an
id for them on top of their account executive number. I also need to keep
this because we also want to be able to being up information based on account
executive, which would should what their individual daily, montly and yearly
total would be. Another thing that I was wondering is that if I would really
need to have like the product name or quantity. We aren't looking to use the
database for that purpose, we really just need to have the amount of each
sale/what was ordered. If it is needed than I would have to have the more
detailed table options because many our orders are broken down by each
indivual part depending on what they are looking for. It might be possible
to kinda classify things with either table, accessories, hardware and so on,
but as I said we are using it more for the financial information. Thanks
again.