UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Input Form From Query Or Table, Access 2016    
 
   
Makya
post Aug 19 2019, 03:35 PM
Post#1



Posts: 18
Joined: 11-July 19



I am a bit confused right now.... After reading, studying and watch numerous Access Tutorials I get various answers for my question. I am creating a Payroll Database. I have an Employee (with Basic Empl info) Table and a Payslip Table (EmpID linked)

Which is the best route to take:

Do I create a Form directly from the Payslip Table and enter the calculations on the Form in the the relative fields

OR

Do I create a query from the table and insert Calculation fields in the query and then create a Form from the Query.

The Form will basically have a PayslipID EmpID(AutoFill the Name and Date Engaged and Current Wage Amount) I will then enter the Hrs Worked. If the hours are entered all the calculations for the relevant Taxes etc must be done via either Calculated fields on the Form or Query.

I then must print many reports and a payslip from info entered on the form via queries from the PayslipTable.
This post has been edited by Makya: Aug 19 2019, 03:37 PM
Go to the top of the page
 
June7
post Aug 19 2019, 03:40 PM
Post#2



Posts: 788
Joined: 25-January 16



Enter raw data via forms. Do calcs for output on report. Whether or not calculated data shows on form is optional. Do as many calcs as possible in query that is basis for report. Yes, query with calcs could be form RecordSource.
This post has been edited by June7: Aug 19 2019, 03:43 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
theDBguy
post Aug 19 2019, 03:40 PM
Post#3


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


Hi. The first question I have to ask is are you planning to store the results of the calculations in any of the table in your database? If so, then we'll need to address that part first before should move on with your question about forms.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Makya
post Aug 19 2019, 03:57 PM
Post#4



Posts: 18
Joined: 11-July 19



Yes I think it needs to be saved in the Payslip Table(?) - or as long as it is saved somewhere so that I can print history reports via queries. (Please advise)
Go to the top of the page
 
theDBguy
post Aug 19 2019, 04:11 PM
Post#5


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


Hmm, it all depends. If the result of the calculations could change over time, then maybe. Otherwise, storing calculated values is generally not necessary nor recommended in a properly designed database. You can always recalculate the value whenever/wherever you need them. For example, if you use a query to produce the calculations, then you can use the same query for your report without having to store the calculated results in a table at all.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Makya
post Aug 19 2019, 04:30 PM
Post#6



Posts: 18
Joined: 11-July 19



I am also thinking that the info will be in the queries as well. It sounds better to go that route then.(?)
Go to the top of the page
 
theDBguy
post Aug 19 2019, 04:39 PM
Post#7


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


Yes, calculations can be performed in queries, forms, reports, or VBA. If you can avoid storing the results in tables, the better.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
June7
post Aug 19 2019, 04:44 PM
Post#8



Posts: 788
Joined: 25-January 16



One justification for saving calculated data is if calcs are very complicated formulas and it is required that result not change even if formula structure changed later. I had this situation for a laboratory database. A LOT of very complicated formulas were programmed so techs no longer had to do manual calcs then enter the result. In essence the calculated results were saved and became the 'raw' data for report output. True, formulas and procedures rarely changed but could and did at least twice in 10 years.

Another situation for saving data that could normally be looked up from a related table is product price. Prices can change and you don't want existing records for product previously bought or sold to be altered. However, price x quantity can be calculated whenever needed and that result not saved to table.

This post has been edited by June7: Aug 19 2019, 04:45 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Makya
post Aug 28 2019, 02:45 AM
Post#9



Posts: 18
Joined: 11-July 19



The change of Prices or rates is my question today. How do I then go about to create the form for Invoices with Unit Prices that will Increase once a year. Do not want previous records to change.
Form Directly form Table(Raw).

Formulas in Form control boxes and edit the formula when Yearly Increase applies? I tested same with Form from Query and previous Entries change when I edit the Formula in the Query.
Go to the top of the page
 
June7
post Aug 28 2019, 01:08 PM
Post#10



Posts: 788
Joined: 25-January 16



Options:

1. save unit price in effect at that time, not the calculated quantity x price - problem with this approach is since the price is changed in Products table there is no history of pricing info, no supporting documentation in db for the saved price

2. create a new price record in products table (or have Products and ProductPricing tables), set old price record as 'inactive' to exclude from combobox list, just save ID and retrieve price in queries that join tables

3. same setup as 2. but use code to find appropriate price based on effective date and order date - gets complicated

This post has been edited by June7: Aug 28 2019, 01:14 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th September 2019 - 09:18 AM