Summarizing sales data

[Fyyzer]

Here's a brief summary of my problem.

Database 1 - daily sales figures for multiple restaurants (fields include: store, date, sales amount, customer count)

Database 2 - Summary database or join file.

In Database 2 I'd like to be able have it show me a layout with the following:

Month - April 2001

Store 1 - Total Monthly Sales(Summarize total sales for 4/2001) & Total Customer Count (Summarize total customer count for 4/2001)

Store 2 - same info as store 1

Store 3 - same info as store 1

I'm just trying to summarize data but I don't know how to tell it WHICH data (or dates specifically) I want to summarize. Is this something that's going to be accomplished with Scriptmaker or do I have to do something ridiculous like make a ton of fields like TotalSalesApr01, TotalSalesMay01, etc.

I've been beating my head against a wall for days now on this and I have a feeling I'm just not looking at it in the right way. Any help would be greatly appreciated.

-Mike

[markpro]

In database 1 create a calculated field. Call it Month.

Monthname(Date) or Month(Date)

In database 2 create either a text field or number field depending on what you did above.

Define a relationship between Database 2 and Database 1. Call it "Store 1".

Database 2::Month = Database 1::Monthname

Create a calculated field in Database 2. Call it "Total Sales Store 1".

= Sum(Store 1::Trans Total)

Where "Trans Total" is the Total field for each transaction.

Create a Drop down list for "Month" in database 2 containing the months of the year. When you change the value in this field it will give you the some of the related transactions in Database 1 for that particular month.

Hope this helps.

[markpro]

Ooops, forgot the year

Month(date)& " "& Year(Date) or Monthname(date) & " " & Year(Date) in Database 1.

In Database 2 you can either create a field to hold a default year and join it to the selected month. Call it "Month Year Calc".

The relationship is then:

Database 2::Month Year Calc = Database 1::Month

[Fyyzer]

Thanks! I hadn't checked the forum for a couple weeks because I thought no one would be able to help me. Thanks again!

[markpro]

No, I would create a separate sum for each related file. Then for the total I would add those 3 sum fields.

[markpro]

I might be missing something, each stores sales are contained in a separate transaction file when I thought about it, otherwise you are correct, you would have to create field that includes the storename instead of Month Year it would have to be Store Month Year for example.

[proton]

quote:

---

Originally posted by markpro:

Ooops, forgot the year

Month(date)& " "& Year(Date) or Monthname(date) & " " & Year(Date) in Database 1.

In Database 2 you can either create a field to hold a default year and join it to the selected month. Call it "Month Year Calc".

The relationship is then:

Database 2::Month Year Calc = Database 1::Month

---

markpro,

will that work? If you set the relationship between monthyear and monthyear name and do a calculation that sums it, then it will display a total sales for the month, but it will be the total for all three stores not so?

I think he wants it to distinguish the store as well. So the field would have to combine the store, the month, and the year and form the relationship with that and then do the calculation field. Then the Sum result will be for the store for that specific date. Hope this helps.