Best setup for this form??

[Hayley Baxter]

Hello everyone

Wondering if any of you can advise me on best way to set up my form.

I have a main form Client, client is chosen from combo box then there is a cmd button to open related accounts for the selected client. This is fine all is working well. I now have my second form named FrmclientAccounts, on here I have a subfrm of all the clientsaccounts. What I would like to know is this, I have a spreadsheet of what is currently used and I need to use this format. A subfrm usually lists all the data in rows but I need to have the layout something like a table with the headings of all the months Jan to Dec and there were would be a total of figures per month/ per quarter/ per year. I am thinking that a subfrm might not be the best way to go about this.

Am I maybe better using the current spreadsheet then linking to access as the sample in the northwind.mdb shows?

Thanks
Hay

 

[Dave Eyley]

Can't you use the subform but in continuous form mode and lay it out to look like a spreadsheet?

Might need the header and footer for various totals etc...

Dave Eyley

 

[Rich]

I'd say if you have a table with the months listed as fields you have a design problem, you only need one field to hold the month, if you want to display as a spreadsheet then use a crosstab query.

 

[RichMorrison]

Hayley,

From what you wrote it sounds like you want a subform with Months as columns and ???? as rows. Maybe ???? is some specific account ID for a client.

In any event;
1) You can make a subform with a datasheet view.
2) But if you use a crosstab query for the subform then the data is not updatable.

One solution is to denormalize the data by creating a field for each month. Your imaginary ClientAccounts table would contain:
Client ID ?
Account ID ?
Jan numbers,
Feb numbers
.....
Dec numbers.

RichM

 

[Hayley Baxter]

Thanks guys I haven't had the chance to have a go at this yet but RichM you are pretty spot on with what I want.

Rich I don't actually have different fields for each month what I have is a client table linked to the ClientAccounts table via a one - many relationship as one client can have many accounts ie they will have different figures for every month so one client will have accounts with different figures from Jan to Dec.

I wanted something like:

Jan / Feb/March (I would like these as columns)

Actual Forecast (this is the columns)

(Rows)
Staff Costs 1,2345 1,2345
Other Costs 1,3456 1,3456

Totals 1,2345 1,3456

Then I need the same for the other months as well.

Thanks again
Hay