[RESOLVED] Simple Account App Layout

[toecutter]

I have a local SQL Server Compact 3.5 DB that will have 12 tables for the year payments and 12 for receipts.

I have got it working with July's table and am not sure if i will have to maunally make another 23 tables or some how reuse it via code.

Can anyone suggest how to save creating another 11 tables for payments and 12 for receipts?

This database will also need to be copied for the next finacial year when it arrives so i am also trying to work out how to make it a template database and then reload next year as a new blank DB.

thanks
toe

[jmcilhinney]

You only need one table for the whole year. Whatever schema you have right now, simply add a Month column to it and then store a number from 1 to 12 in it to indicate which month of the year it relates to. If you want all of July's records then you simply filter by that column where the value is 7.

Again, you don't need to copy anything for other years. You simply add a Year column to the table as well. If you want data from March 2010 then you filter where the Month is 3 and the Year is 2010.

[toecutter]

hey jm,

thanks for that.

So based on what you say there appears there is no reason i cannot have ONE column with a date formatted as 12/12/2013 and filter from that?

[jmcilhinney]

Absolutely. You can filter by a range of dates based on month or year.

[techgnome]

That's how most of us do it. It may seem easier to have things segmented out... until the first time you have to combine data from multiple months, or years even...

-tg