Exporting to excel - organize by month

[Jojojo]

I have figured out how to link the query and import it into an excel speadsheet.
I'm not sure the best way to set it up to organize it the way that I need in excel.

The spreadsheet is to track income. So all
transactions get exported. In the excel
speadsheet there is a section for January and all the transactions would list unde there. Then below would be february etc, for the whole year

how do I get it to put the right transactions under each month? Do I have to set up a query in access for each month? Or
do I just import the data 12 times and filter it differently each time? I feel like there is a much easier way and I'm overthinking.

Thanks.

[June7]

Why Excel? Why not build reports in Access?

[Jojojo]

Why Excel? Why not build reports in Access?

The client has an excel spreadsheet system that she uses to track income & expenses and calculates net income etc. She has other income that doesn't come from the database that she also uses the same spreadsheet to keep track of.

[June7]

Personally, I would get it all into Access somehow. Organizing the data in the Excel as you described might require a lot of fancy code, especially if the data under each month keeps growing.

You are linking from the Excel side into Access tables? Twelve links at various positions of the sheet might work. Just don't know if one will run into the next when data is refereshed. Never tried this. You should test it and let us know.

[Jojojo]

I actually tested the refresh before I posted. It doesn't overlap - it seems to know to insert it before the Sum field - so that part works.

But I just started thinking about how it will work for next year, the year after etc... I would have to have multiple queries. Maybe I will try and set it up in Access.....

[Jojojo]

June7, I'm having trouble with the Income section of this. I have set up the Expense section with no problem at all. I'm not sure how to Join multiple incomes into 1 query or table. (This is for the same fitness gym you've helped me on in the past)

I have a table for Fitness Registrations, which saves the date, amount, name of member.

I have a table for Massage, which saves the date, amount, name of member. (these two tables have a common MemberID from the Members table)

Now - I've started trying to add the income and expenses as you suggested.

I had to set up an Income table, for the user to input the other types of income - that are not member specific. Such as Retail space rent, or room rents, misc etc - This table is not related to the Fitness, Massage or Member tables.

I need a report that will pull all three of those tables together, to give me a total income report.

Is there a way using a join to do this? Or do I have to re-design the database?

[June7]

Did you consider existing packages? Might want to investigate:
http://www.fastfunfitness.com/fitness_club_software.htm
http://ptf.com/gym/gym+membership+da...mplate+access/
http://www.efitfinancial.com/
http://windows.podnova.com/software/625751.htm

I would have one Receipts table. With fields like:
ID
PaymentDate
PaymentAmt
PaymentType (Cash, CC, Check)
Reference (check#, CCTrans#)

Using as a model the club I attend - each person has a unique memberID. Multiple MemberIDs (spouses, children) can be tied to one billing account. Individual loses member card, void it and assign a new MemberID and attach to the same account. So anyone, member or not, who does business with the club should have an account number. There would probably need to be an account for anonymous over the counter purchases.

Are members allowed to charge on account purchases from club cafe, pro shop, massage and tanning packages? Non-member commitments are also charged on account? These charges would be recorded as accounts receivable and billed to the account holder. Payments toward the balance would be documented by reference in the account record to the RecieptID.

Otherwise, having receipts info spread out over multiple tables will require a UNION query. Need for UNION query is one indicator of bad db design. There is no wizard or designer for UNION query, must type it into the SQL View editor of query designer.

Net Income/Loss would be the difference between Receipts and Expenses.