Re: [MSAccessProfessionals] UPDATE TABLE issue

Thanks Again.

On Tuesday, December 22, 2020, 03:35:30 PM CST, Paul Baldy <pbaldy@gmail.com> wrote:

Happy to help Sean!  JasonM has a FAQ on the subject here:

http://www.baldyweb.com/BuildSQL.htm

Paul

------ Original Message ------

From: "Sean Cooper via groups.io" <smcjb=yahoo.com@groups.io>

To: "MSAccessProfessionals@groups.io" <msaccessprofessionals@groups.io>

Sent: 12/22/2020 11:44:15 AM

Subject: Re: [MSAccessProfessionals] UPDATE TABLE issue

Thanks Paul. That fixes it. Is there an easy explanation as to why it needed the ' deliminaters?

On Tuesday, December 22, 2020, 01:32:06 PM CST, Paul Baldy <pbaldy@gmail.com> wrote:

The value needs delimiters.  Try

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = '" & myString & "'"

Paul

------ Original Message ------

From: "Sean Cooper via groups.io" <smcjb=yahoo.com@groups.io>

To: "MSAccessProfessionals@groups.io" <MSAccessProfessionals@groups.io>

Sent: 12/22/2020 11:17:14 AM

Subject: [MSAccessProfessionals] UPDATE TABLE issue

The following works fine

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim mylong As Long

TableToModify = "ABC"

NewColName = "NewCol20"

mylong = 1122

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " Long"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & mylong

End Sub

But when I try and modify it to update with a string or text instead of a Long I get a Run-Time error 3061, Too few parameters. Expected 1. I've tried changing the ALTER TABLE to CHAR(14), Dim'ing the String with no size all with no luck.

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim myString As String * 14

TableToModify = "ABC"

NewColName = "NewCol20"

myString = "ABCD1234567890

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " TEXT(14)"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & myString

End Sub

Any help appreciated.

Thanks

Sean

Re: [MSAccessProfessionals] UPDATE TABLE issue

Happy to help Sean!  JasonM has a FAQ on the subject here:

http://www.baldyweb.com/BuildSQL.htm

Paul

------ Original Message ------

From: "Sean Cooper via groups.io" <smcjb=yahoo.com@groups.io>

To: "MSAccessProfessionals@groups.io" <msaccessprofessionals@groups.io>

Sent: 12/22/2020 11:44:15 AM

Subject: Re: [MSAccessProfessionals] UPDATE TABLE issue

Thanks Paul. That fixes it. Is there an easy explanation as to why it needed the ' deliminaters?

On Tuesday, December 22, 2020, 01:32:06 PM CST, Paul Baldy <pbaldy@gmail.com> wrote:

The value needs delimiters.  Try

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = '" & myString & "'"

Paul

------ Original Message ------

From: "Sean Cooper via groups.io" <smcjb=yahoo.com@groups.io>

To: "MSAccessProfessionals@groups.io" <MSAccessProfessionals@groups.io>

Sent: 12/22/2020 11:17:14 AM

Subject: [MSAccessProfessionals] UPDATE TABLE issue

The following works fine

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim mylong As Long

TableToModify = "ABC"

NewColName = "NewCol20"

mylong = 1122

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " Long"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & mylong

End Sub

But when I try and modify it to update with a string or text instead of a Long I get a Run-Time error 3061, Too few parameters. Expected 1. I've tried changing the ALTER TABLE to CHAR(14), Dim'ing the String with no size all with no luck.

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim myString As String * 14

TableToModify = "ABC"

NewColName = "NewCol20"

myString = "ABCD1234567890

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " TEXT(14)"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & myString

End Sub

Any help appreciated.

Thanks

Sean

Re: [MSAccessProfessionals] UPDATE TABLE issue

Thanks Paul. That fixes it. Is there an easy explanation as to why it needed the ' deliminaters?

On Tuesday, December 22, 2020, 01:32:06 PM CST, Paul Baldy <pbaldy@gmail.com> wrote:

The value needs delimiters.  Try

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = '" & myString & "'"

Paul

------ Original Message ------

From: "Sean Cooper via groups.io" <smcjb=yahoo.com@groups.io>

To: "MSAccessProfessionals@groups.io" <MSAccessProfessionals@groups.io>

Sent: 12/22/2020 11:17:14 AM

Subject: [MSAccessProfessionals] UPDATE TABLE issue

The following works fine

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim mylong As Long

TableToModify = "ABC"

NewColName = "NewCol20"

mylong = 1122

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " Long"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & mylong

End Sub

But when I try and modify it to update with a string or text instead of a Long I get a Run-Time error 3061, Too few parameters. Expected 1. I've tried changing the ALTER TABLE to CHAR(14), Dim'ing the String with no size all with no luck.

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim myString As String * 14

TableToModify = "ABC"

NewColName = "NewCol20"

myString = "ABCD1234567890

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " TEXT(14)"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & myString

End Sub

Any help appreciated.

Thanks

Sean

Re: [MSAccessProfessionals] UPDATE TABLE issue

The value needs delimiters.  Try

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = '" & myString & "'"

Paul

------ Original Message ------

From: "Sean Cooper via groups.io" <smcjb=yahoo.com@groups.io>

To: "MSAccessProfessionals@groups.io" <MSAccessProfessionals@groups.io>

Sent: 12/22/2020 11:17:14 AM

Subject: [MSAccessProfessionals] UPDATE TABLE issue

The following works fine

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim mylong As Long

TableToModify = "ABC"

NewColName = "NewCol20"

mylong = 1122

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " Long"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & mylong

End Sub

But when I try and modify it to update with a string or text instead of a Long I get a Run-Time error 3061, Too few parameters. Expected 1. I've tried changing the ALTER TABLE to CHAR(14), Dim'ing the String with no size all with no luck.

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim myString As String * 14

TableToModify = "ABC"

NewColName = "NewCol20"

myString = "ABCD1234567890

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " TEXT(14)"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & myString

End Sub

Any help appreciated.

Thanks

Sean

[MSAccessProfessionals] UPDATE TABLE issue

The following works fine

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim mylong As Long

TableToModify = "ABC"

NewColName = "NewCol20"

mylong = 1122

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " Long"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & mylong

End Sub

But when I try and modify it to update with a string or text instead of a Long I get a Run-Time error 3061, Too few parameters. Expected 1. I've tried changing the ALTER TABLE to CHAR(14), Dim'ing the String with no size all with no luck.

Public Sub MyAddColumn()

Dim TableToModify As String

Dim NewColName As String

Dim myString As String * 14

TableToModify = "ABC"

NewColName = "NewCol20"

myString = "ABCD1234567890

CurrentDb.Execute "ALTER TABLE " & TableToModify & " ADD COLUMN " & NewColName & " TEXT(14)"

CurrentDb.Execute "UPDATE " & TableToModify & " SET " & NewColName & " = " & myString

End Sub

Any help appreciated.

Thanks

Sean

Re: [MSAccessProfessionals] Custom Ribbon not showing

can you use ActivateTab.

gobjRibbon.ActivateTab "idOfMyTab".

http://www.accessribbon.de/en/index.php?Ne..._in_Access_2010

On Sat, Dec 19, 2020 at 10:00 AM Bill Mosca <wrmosca@myself.com> wrote:

Hi Crystal

Sendkeys would really have to be a last resort. They are so unreliable. I sent out instructions to all my users. Maybe that will be good enough until I stumble upon a solution.

On Sat, Dec 19, 2020 at 07:04 AM, crystal (strive4peace) wrote:

hi Bill !

I'm no ribbon expert, but SendKeys is always a last resort, if your custom ribbon has a hotkey

kind regards,
crystal

 

On 12/18/2020 5:40 PM, Bill Mosca wrote:

On Fri, Dec 18, 2020 at 09:39 AM, Bill Mosca wrote:

Arnelito - Thank you for taking the time to send me a sample. After turning on the "Show add-in user interface errors" (which I thought I already turned on), I found I had some syntax errors in my XML. I'll work on those and see if that fixes my issue.

Again, thanks for taking the time to help.
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

I got it all working. One last little tweak. The custom report ribbon loads but the Home tab still has the focus. Does anyone know how to use a command to put the focus on the custom tab? It would be best that the user sees it when thte report opens in Preview.
 
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

 
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

--

Arnelito G. Puzon

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115825) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] Custom Ribbon not showing

Hi Crystal

Sendkeys would really have to be a last resort. They are so unreliable. I sent out instructions to all my users. Maybe that will be good enough until I stumble upon a solution.

On Sat, Dec 19, 2020 at 07:04 AM, crystal (strive4peace) wrote:

hi Bill !

I'm no ribbon expert, but SendKeys is always a last resort, if your custom ribbon has a hotkey

kind regards,
crystal

 

On 12/18/2020 5:40 PM, Bill Mosca wrote:

On Fri, Dec 18, 2020 at 09:39 AM, Bill Mosca wrote:

Arnelito - Thank you for taking the time to send me a sample. After turning on the "Show add-in user interface errors" (which I thought I already turned on), I found I had some syntax errors in my XML. I'll work on those and see if that fixes my issue.

Again, thanks for taking the time to help.
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

I got it all working. One last little tweak. The custom report ribbon loads but the Home tab still has the focus. Does anyone know how to use a command to put the focus on the custom tab? It would be best that the user sees it when thte report opens in Preview.
 
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

 
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115824) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] Custom Ribbon not showing

hi Bill !

I'm no ribbon expert, but SendKeys is always a last resort, if your custom ribbon has a hotkey

kind regards,
crystal

On 12/18/2020 5:40 PM, Bill Mosca wrote:

On Fri, Dec 18, 2020 at 09:39 AM, Bill Mosca wrote:

Arnelito - Thank you for taking the time to send me a sample. After turning on the "Show add-in user interface errors" (which I thought I already turned on), I found I had some syntax errors in my XML. I'll work on those and see if that fixes my issue.

Again, thanks for taking the time to help.
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

I got it all working. One last little tweak. The custom report ribbon loads but the Home tab still has the focus. Does anyone know how to use a command to put the focus on the custom tab? It would be best that the user sees it when thte report opens in Preview.
 
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

Re: [MSAccessProfessionals] Custom Ribbon not showing

On Fri, Dec 18, 2020 at 09:39 AM, Bill Mosca wrote:

Arnelito - Thank you for taking the time to send me a sample. After turning on the "Show add-in user interface errors" (which I thought I already turned on), I found I had some syntax errors in my XML. I'll work on those and see if that fixes my issue.

Again, thanks for taking the time to help.
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

I got it all working. One last little tweak. The custom report ribbon loads but the Home tab still has the focus. Does anyone know how to use a command to put the focus on the custom tab? It would be best that the user sees it when thte report opens in Preview.
 
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115822) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] Custom Ribbon not showing

sample report with ribbon

On Thu, Dec 17, 2020 at 4:44 PM Bill Mosca <wrmosca@myself.com> wrote:

On Thu, Dec 17, 2020 at 04:16 PM, Arnelito Puzon wrote:

each Form/Report has "Ribbon Name" property (Property->Other).

you put the name of your custom ribbon there.

On Thu, Dec 17, 2020 at 12:27 PM Bill Mosca <wrmosca@myself.com> wrote:

This is my first time using a custom ribbon and I can't get things to jive. You know, old dogs, new tricks stuff I guess.

 

The main ribbon is limited to just the Home tab. It uses XML that Daniel Pineault wrote years ago. That part works. Here is the XML:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

 <ribbon startFromScratch="true"> 

  <tabs>

   <tab id="tabMain" label="Home">

    <group id="grpMain" label="Main">

     <button id="cmdHome" label="Home" onAction="OnActionButton" size="large" imageMso="OpenStartPage"/>

    </group>

   </tab>

  </tabs>

 </ribbon>

 </customUI>

 

 

In File tab >Options>current database, the ribbon is selected as the Ribbon Name. That ribbon loads as expected. 

 

All my reports have a different custom ribbon chosen as the report's ribbon. It has various buttons for printing, emailing as attachment, saving results to spreadsheet, etc. Each button calls a function. I used Gunter Aveniius's Ribbon Creator (2013-2016). All I really need is to it load onto the main ribbon, or even replace the main ribbon as long as the report is open in preview.

 

My problem is the report ribbon is not loading onto the main ribbon. The property Start from scratch is set to false. I've tried setting it to tru but that didn't make any difference.

--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

 

 

 

--

Arnelito G. Puzon

Arnelito - Thank you for taking the time to respond. I did put the name of the ribbon in the report's property sheet by selecting it from the dropdown. As far as I can see, I am not doing anything in VBA code that would block this custom ribbon.
 
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

--

Arnelito G. Puzon

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115817) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] Custom Ribbon not showing

Arnelito - Thank you for taking the time to send me a sample. After turning on the "Show add-in user interface errors" (which I thought I already turned on), I found I had some syntax errors in my XML. I'll work on those and see if that fixes my issue.

Again, thanks for taking the time to help.
--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115821) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] Custom Ribbon not showing

each Form/Report has "Ribbon Name" property (Property->Other).

you put the name of your custom ribbon there.

On Thu, Dec 17, 2020 at 12:27 PM Bill Mosca <wrmosca@myself.com> wrote:

This is my first time using a custom ribbon and I can't get things to jive. You know, old dogs, new tricks stuff I guess.

 

The main ribbon is limited to just the Home tab. It uses XML that Daniel Pineault wrote years ago. That part works. Here is the XML:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

 <ribbon startFromScratch="true"> 

  <tabs>

   <tab id="tabMain" label="Home">

    <group id="grpMain" label="Main">

     <button id="cmdHome" label="Home" onAction="OnActionButton" size="large" imageMso="OpenStartPage"/>

    </group>

   </tab>

  </tabs>

 </ribbon>

 </customUI>

 

 

In File tab >Options>current database, the ribbon is selected as the Ribbon Name. That ribbon loads as expected. 

 

All my reports have a different custom ribbon chosen as the report's ribbon. It has various buttons for printing, emailing as attachment, saving results to spreadsheet, etc. Each button calls a function. I used Gunter Aveniius's Ribbon Creator (2013-2016). All I really need is to it load onto the main ribbon, or even replace the main ribbon as long as the report is open in preview.

 

My problem is the report ribbon is not loading onto the main ribbon. The property Start from scratch is set to false. I've tried setting it to tru but that didn't make any difference.

--

Regards,

Bill Mosca, Founder - MS_Access_Professionals

Microsoft Office Access MVP 2010-2016

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

 

--

Arnelito G. Puzon

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115815) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Re: [MSAccessProfessionals] row source and auto numbering

Actually,

You did a great job with the specs. I am only lost in two areas of your specs.

(************************ Part 1 Beginning Here ***************************)

My data should be like this
Task ID : 1
- Sub Task ID : 1
- Sub Task ID : 2
- Sub Task ID : 3
- Sub Task ID : 4
Task ID : 2
- Sub Task ID : 1
- Sub Task ID : 2
- Sub Task ID : 3
So when I go to add a Sub Task to Task 1, the new Sub Task ID should be 5.
And when I go to add a Sub Task to Task 2, the new Sub Task ID should be 4.

(**************************** Part 1 Ending Here********************************)


Mr smiley2004_10 <cas23121964@gmail.com>

I think the Part 1 as I have identified above and I think clearly to all who view your PDF.
You should simply delete. Get rid of it entirely. It is not a spec. It is an excel users visibility problem.
What I mean is you now have a much more enterprise worthy tool you are maybe
writing your first application with this tool (MS Access Development).

You have done an outstanding job with your drop down lists your referencing in the Screen layout
Grids, and those tables you created perhaps using word or maybe excel with highlighting are great.
I totally understand them.

Now per my comment that its an excel users visibility problem. What I mean is that the majority
of people like viewing task numbers so perfectly formed and number as you show on what I
view as some crazy wish list. You do not need to have those numbered in that manner at all.
You are using a database tool and creating forms using the MS Access forms designer.
You are suppose to hide data. Since that is true. Who cares if a perceived task 1 is under the
hood actually maybe say NUMERIC VALUE 20 - no one in the world needs to know or care just as long
as those numbers tie lets say JOIN (DATABASE TERMINOLOGY) your parent table to its sub/child table.

Your users will never see these values. You are breaking away from visual cues that need to please
human beings or they will fall apart, lack confidence etc... You need to keep those people away from
the glue that JOINS your tables together. Simple auto-number values performed sequentially
are going to do the job. Keep those foxes out of the chicken house as you are now the man the one
who in the future will fix repair any program or system failures which I doubt will ever occur.

You are treating a great tool as if it must reveal all to the users. You broke that contract
when you chose a real tool that encourages even vilifies such a need.
You now have your own deal your own glue (your table design) holding the relationships
of you
r tables together. Please do not try to please those people who want to remain
using excel. You are making way to much effort on making the database provide
some non-existing need to visually please those who do not understand that they need not
know any of those links the exact numbers ever again. Its now entered the abyss
of them needing to do absolutely nothing not ever needing to see values that add nothing to the
desired end result except that they if ever seen would look really great and orderly.
The relational model provides that glue. Never ever reveal the glue. Tell them to stay in their lane.
The design of your tables is in my view par-ex-cell-lent NOT TRYING TO BE EXCEL a more visible tool.
You can certainly hide things in excel too and some designers should probably do so in that tool as well.

The only other disconnect that I had was you did not define what the p or the m of "p/m" means.
Also the entire excel sheet of values you purport to have meaning is unclear to me as why did you not provide
headers and row numbers. Then using those coordinates describe any calculated results perhaps even using
excel references/calculations even. I could probably understand some writing that you could also provide
mentioning Column E row 12 containing excel like calc B12*1.5(result of Drop down selection from table/list xyz)
You could probably dial us all in on the Highlighted resultant fields (proposed output) of what will be
a micro-soft forms output or maybe a MS Access Report. I have no idea your intended output since you gave us
all an excel sheet with no headers no row numbers/anchoring values how could anyone follow that?

If you tag on column headers and row identity of some sort and just give proposed calculations we could probably
follow you to a conclusion and likely understand an intended programmed result.

Your 95% done just some tweaking and we all slide into home plate.

Excellent clean specs. Please delete unnecessary work items to please those who don't want you to use MS Access.
Give anchoring headings for rows columns and present calculations using co-ordinates to describe proposed
results. Slide us all into home plate...

Regards,

Mark A Stacklin

On Sunday, December 13, 2020, 06:44:14 PM EST, smiley2004_10 <cas23121964@gmail.com> wrote:

Hi

 

I would like to achieve the following on a form view and the ability to calculate each sub row task.

 

As can be shown I have also given the table structure and input and out put. The calculation starts a $50

Is this possible using Access and SQl

 

 

My data should be like this
Task ID : 1
- Sub Task ID : 1
- Sub Task ID : 2
- Sub Task ID : 3
- Sub Task ID : 4

Task ID : 2
- Sub Task ID : 1
- Sub Task ID : 2
- Sub Task ID : 3

So when I go to add a Sub Task to Task 1, the new Sub Task ID should be 5.

And when I go to add a Sub Task to Task 2, the new Sub Task ID should be 4.

 

 

Input screen form - please find uploaded 

 

Output – as stated the calculation starts at $50 if the price is above the clients tolerance of $432p/m in this case yes the value based price is $855.36 p/m

It is then added to the final price structure before options are added or behaviour values and discounts

 

 

 

 

Tables

 

Questions

 

Question	Answer Type	Value Type	Yes Value	No Value	Default Value	Dropdown list or Unit price question
About your business
What is your annual sales?	Dropdown	Value				Annual accounts turnover value (FEB)
What type of business do you have?	Dropdown	Multiplier				Annual accounts business type multiplier (FEB)
What is your trading entity?	Dropdown	Multiplier				Annual accounts trading entity multiplier (FEB)
How many owners are there?	Value	Unit Price
Are you registered for sales tax (VAT/GST)?	Yes or No	Percentage Increase	10	0	Yes value
About your accounting system
What accounting system do you have?	Dropdown	Multiplier				Annual accounts accounting system multiplier (FEB)
Do you have a fully balanced trial balance?	Yes or No	Percentage Increase	30	0	Yes value
Do all your bank accounts reconcile?	Yes or No	Percentage Increase	-20	0	Yes value
Do you have personal out of pocket expenses?	Yes or No	Percentage Increase	5	0	Yes value
Do you have a payroll scheme?	Yes or No	Percentage Increase	10	0	Yes value
Do you have inter-company accounts?	Yes or No	Percentage Increase	10	0	Yes value
Do you have overseas sales?	Yes or No	Percentage Increase	5	0	Yes value
What is the quality of your records?	Dropdown	Percentage Increase				Annual accounts quality of records percentage (FEB)
About the service you want
Do you want our 30-day FastTrack service?	Yes or No	Percentage Increase	10	0	Yes value
How many earlier years' accounts do you need?	Dropdown	Percentage Increase				Annual accounts earlier years accounts percentage (FEB)
Who would you like to be assigned to?	Dropdown	Multiplier				Annual accounts seniority of accountant multiplier (FEB)

 

What follows are the lists created for each of the Dropdown questions.

Lists

Annual accounts turnover value (FEB)

1 - 80,000	840
80,001 - 150,000	1440
150,001 - 500,000	2100
500,001 - 1,000,000	3000
1,000,001 - 2,000,000	4200
2,000,001 - 5,000,000	6900
5m+	10200

 

Annual accounts business type multiplier (FEB)

Manufacturing	1
Bars, cafes and restaurants	1.2
Retail	1.1
Construction	1.2
Professional service business	1
Property dealer	0.8
Other	1

 

Annual accounts trading entity multiplier (FEB)

Sole trader	1
Partnership	1.2
Limited Liability Partnership	1.2
Limited Company	1.2
Other (including Limited by Guarantee)	1.2

 

Annual accounts accounting system multiplier (FEB)

QuickBooks Online	0.8
Manual	1.5
Excel spreadsheet	1.2
Desktop accounting system	1.1
Xero	0.9
Other cloud accounting system	1

 

Annual accounts quality of records percentage (FEB)

Poor	50
Average	0
Excellent	-10

 

Annual accounts earlier years accounts percentage (FEB)

Not applicable	0
1 year	80
2 year	150

 

Annual accounts seniority of accountant multiplier (FEB)

Partner	1
Senior manager	0.75

 

Expressing Price

Suggested settings are:

 

Minimum price:                   1000

Maximum price:  50000

Price descriptor:   Your small monthly investment is just…

Pricing of packages

 

Description	Adjustment Type	Price Adjustment	Rounding On	Rounding Amount	Period Spread Over
Lite	Relative	0.75	OFF		12
Entry Accounting	Relative	1	ON	9	12
Full Accounting	Relative	1.3	ON	7	12
Advanced Accounting	Relative	1.5	ON	9	12

 

Rewards

 

Question	Value Type	Yes Value	Default Value
Behaviour rewards
Manual/Excel: Will you provide a balanced trial balance?	Percentage decrease	20	No value
Do you want us to do the bookkeeping?	Percentage decrease	30	No value
Get a 15% discount when you pay us in full in advance	Percentage decrease	15	No value
Will you refer us to 3 other businesses like yours?	Fixed discount	500	No value

 

Options

 

Question	Description	Value Type	Yes Value	Default Value
Optional extra services
Annual 5-year trend analysis report	Only $17 per month when added on to your annual accounts	Value	200	No value
Audit		Percentage increase	20	No value
Cash reconciliation report	Only $42 per month when added on to your annual accounts	Value	500	No value
Goal review meeting	Only $42 per month when added on to your annual accounts	Value	500	No value
PAYE/NIC and VAT health checks	Only $83 per month when added on to your annual accounts	Value	1000	No value
Preparation of sales tax returns	Only $83 per month when added on to your annual accounts	Value	1000	No value
Pre-year end tax planning review	Only $42 per month when added on to your annual accounts	Value	500	No value
Profit improvement planning meeting	Only $42 per month when added on to your annual accounts	Value	500	No value
Quarterly business strategy meetings	Only $167 per month when added on to your annual accounts	Value	2000	No value

 

 

Adrian 

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115820) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_