Monthly Reports

You can connect the the SQL server via ODBC from Excel.

Data | Database Query

Create a new database source using the SQL Server driver, enter your server/database paramaters, and you are off the races. MS Query will open and you can create queries from there. Then you can slice and dice the data any way you want it or do Pivottables.

Jason

Well that really depends on the columns you want to see and how your business process works... I'll explain why...

You can get all of the daily (batch) sales information out of the BATCH table. This will provide totals only. You can set up a criteria where year(ClosingTime) 06 and month(ClosingTime) for example. This will give you the total sales, tax, etc. for each batch that was closed during the month.

On the other hand, sometimes transactions could be run after closing the batch on the last day of the month. If you want to pick up these transactions, you have to use another method. I tell cashiers that after the Z on the last day of the month, there should be no more transactions, but it could happen. This would require using the Transaction or TransactionEntry tables, and this give you a lot more data.

I use the TransactionsEntry table most often as my starting point. You will need to determine what other columns you want to pick up to determine what tables you need and to set up the links. Here are some common ones:

Tables Transaction Item Department Category

Links Transaction.TransactionNumber - TransactionEntry.TransactionNumber Item.ID - TransactionEntry.ItemID Item.DepartmentID - Department.ID Item.CategoryID - Category.ID

Warning: MSQuery likes to guess the link when you add a table (and always guesses wrong). So delete the guessed links and start from scratch.

As far as field you want to pick up, you might want some calculated date fields, especially if you are pulling multiple months. I usually bring in year(Transaction.Time), month(Transaction.Time), and day(Transaction.Time).

You are going to get a lot of data - one row for each line item on each sale. The TransactionEntry table is just that - one row per receipt row. The good news is that Excel is great at slicing and dicing the date to summary levels that you require.

Jason, don't forget the StoreID in both [Transaction] and TransactionEntry since TonyC stated that he wants to access HQ database in his first post