Tables/Fields issue

[melonye]

Hi all,

I have forgotten quite a bit of Access. I am creating a DB for a practice where they only want to track file status and fee status of all their services.

Suppose, they have a payroll table- fields are payroll duration, file status and fee status. Now, payroll duration has look up wizard values-monthly, quarterly, weekly, annually. When the user selects monthly, it should display fields from Jan, Feb,.., Dec with two more fields FileStatus(yes/no) and FeeStatus(paid/pending).

I am confused as to whether I create a table with months from Jan, Feb,... Dec. Another table with week dates and another for months in quarter.

Please advice.

Thanks.

 

[Mihail]

I am confused as to whether I create a table with months from Jan, Feb,... Dec. Another table with week dates and another for months in quarter.
Thanks.

no No and NO ! Don't do this.
Seems that a Cross Tab query should do the trick.

 

[melonye]

Oh, Thanks

I will try that.

 

[melonye]

I don't want to just display the data. That did not work for me or I don't know how to do it
I have a table client with company name, tax no etc and another table vatreturns
.I have drop box with options monthly In vat returns and when it is selected, rows with jan to dec is to be displayed so that user can entered data if for example Jan there need checkboxes with file and fee.Similarly if quarterly is displayed then apr,jul etc will be shown with fee and file. Each client if monthly should have data from Jan to Dec and if quarterly then only apr, jul and so on.


I am totally lost with the above suggestion. I am quite bad at access

 

[CJ_London]

I don't think Mihail has understood

I'm not quite sure myself...

Your first post refers to payroll and now you are referring to vat returns. Your first post refers to week dates which would not be appropriate for vat

You need to be clearer about what you want to do. The two requirements will have completely difference solutions. Months is pretty straightforward, but quarterly less so - unless quarters are always apr, jul, oct, jan. But week numbers will vary from year to year - and sometimes you have a 53 week year.

So going with your latest post, if I understand you correctly, you want to populate the rowsource for a combo box dependant on another value.

In this case the value is the vatreturns field?

And dependant on that value (monthly, quarterly) you want the user to be able to select from a list of Jan, Feb, Mar... or Q1, Q2, Q3... respectively

And I don't understand what you mean by

if for example Jan there need checkboxes with file and fee

Does this mean it does not apply for some or all the other months or doesn't apply for quarterly? or what?

 

[Mihail]

@CJ.
Right

 

[melonye]

Oh dear ! Sorry about the confusion.

I have a table client and another table with list of services then these services have their own tables like payroll, vat returns and so on.
Each service like payroll table or vat returns and so on have a column callled duration which I can either make a separate table or give drop down option like weekly, bi monthly, monthly and so. Irrespective of payroll or vat returns, etc , they all have duration(except for client and service table) and these duration if option is selected monthly should should month name and not dates like Jan, Feb,Mar,etc. If weekly then again Jan but under Jan it should show week01,week02 and so on.

After the option has been selected, suppose monthly then each month has two more options filed and paid.

I am not sure if I should make duration a separate table as rite now it's a col name in each of tables which offer services.
Also, then should I have separate tables for months like Jan,Feb, etc as how will data be stored of whether that month it been filed or paid.

Please help. Thanks.

 

[melonye]

Also, if month selected they will always be Jan,Feb,Mar. If quarterly then Apr, and so on. This does not change if two clients have duration as quarterly they both will show Apr,Jul etc and whether it is filed or paid.

I figured if I understand monthly, then I can work on others. Don't want everything spoon fed. Have to learn something lol.

Thanks.

 

[CJ_London]

Sorry, you still haven't answered the question

Are you looking for a way to populate a combobox row source with Jan, Feb, Mar etc or Apr, Jul etc based on the value in another control?

If yes then there are other questions that need to be asked, if no, please explain clearly, give a specific example. If possible, provide a screenshot of your form.

I am confused as to whether I create a table with months from Jan, Feb,... Dec. Another table with week dates and another for months in quarter.

Depending on the above will enable us to answer this question

 

[melonye]

Sorry, you still haven't answered the question

Are you looking for a way to populate a combobox row source with Jan, Feb, Mar etc or Apr, Jul etc based on the value in another control?

If yes then there are other questions that need to be asked, if no, please explain clearly, give a specific example. If possible, provide a screenshot of your form.

Depending on the above will enable us to answer this question

I have not yet done fields/rows for individual months as I don't know how I will populate them further.
Right now I have row Duration which I populate with lookup wizard in the design view with monthly, weekly, quarterly, etc.

I don't know what to do after that.

Duration: Monthly

Monthly​

Jan Feb Mar​

Filed Paid Filed Paid Filed Paid​

Does this help?

Do I create one table for jan,feb,etc with two rows ?

 

[CJ_London]

I still cannot envisage what you are trying to do. But if you want a way to populate a combo box with a different set of options there are a number of ways

Whichever way you go, you will need to use VBA.

One way would be to have your VAT return control to be a combo box which might be a value list with the following

"Monthly";"'Jan';'Feb';'Mar'....;'Dec'";"Quarterly";"'Apr';'Jul';'Oct';'Dec'"
NOTE the use of single and double quotes and semi colon, you will also need to complete the missing months - what you should have is a total of 4 text values, surrounded with double quotes and separated by a semi colon.

Then set the column count to 2, the column widths to 1;0 and the bound column to 1

then in the after update event of the vatreturn combo box put the following code

myOtherCombo.rowsource=vatreturn.column(1)

myOthercombo should have columns count and bound column both set to 1