I have 2 tables, one table is for the form to input records; and the other table is for keeping balances. How can I get my Balances table to update as new entries (+ or -) is entered in the input table? Any assistance is greatly appreciated!!!! =D
24 1806 nico5038 3,080
Recognized Expert Specialist
Guess you need to start with reading the normalization article you can find at: http://bytes.com/topic/access/insights/
as it's not necessary to update your balance table.
The proper balance can be deducted when needed.
Nic;o)
Hi Nic;o),
I am not sure what you mean by "The proper balance can be deducted when needed." Can you explain a little.
Thanks!
Nico,
I was able to find the Normalization article. The theories of practice makes sense. I am not sure how this will solve my question.
I have created the input table, in which it has all the records for me to pull into a report. The reason I was wonder if the Balance table can be updated, is because I have more then one type of Item. i.e. Chairs has 100, windows has 100, etc. a total of 10 items. How would I update this quantity based on add ins to the stock and removal? There is no specific sequence a record is entered. Lets say I make an entry of 2 Chairs. Since I am adding 2 chairs then how would I create something to update my balance?
nico5038 3,080
Recognized Expert Specialist
For getting the balance you'll have to sum the created/bought Item quantity (Let's call it qtyIn in tblIn) and to subtract the delivered quantity (Let's call that qtyOut in tblOut).
As both the tblIn and tblOut will hold the ItemID (chair, etc.) you can JOIN these two tables to the tblItems by the ItemID.
By making the query a group by query you can sum the qtyIn and qtyOut for every different Item and by subtracting them the balance is known.
Getting the idea ?
Nic;o)
That makes sense. =D
Okay, I have one more question. Every two weeks I need this qtyIn to increase by 6. Will I need to write some Code to make this happen? or is it possible to have a qry to this process?
NeoPa 32,568
Recognized Expert Moderator MVP
A query can do the update, but scheduling such things is another matter of course. Your specification of every two weeks is far too ambiguous to work from though.
Well, that is interesting, what if I added to a form on the onOpen event to run a query and use the DateDif to calculate the amount of days from the begining of the yr.
nico5038 3,080
Recognized Expert Specialist
There's a big disadvantage in automating such an append, as people tend to get the idea they lose control.
I would probably test when starting the application or the last update has been over two weeks ago to pop-up a confirmation for for the user to agree to write the new quantity to the database. I would also have a control table where the user can set their increment, thus any future changes don't imply changing the code.
This code will of course trigger an append query to insert the quantity.
When you add these quantities into the future, then don't forget to make the query for calculating the balance dependent on the current date.
Nic;o)
Hmm... Thanks for the guidance, I will continue and see how this turns out.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Philip Mette |
last post by:
I have been searching many postings and I cant seem to find anyone
that has this answer so I decided to post. I am using SQL
(Transact-SQL).If I have 2 tables with columns
acct_num,activity_date,and pay_amt and I want to delete one instance
of a record in table 1 for every instance of that record in table 2
how could I do that. For example.
Table 1
-----------
acct activity_date pay_amt
|
by: Colm O'Hagan |
last post by:
Hi there,
I having a problem with a database I'm setting up, I would be
delighted if someone out there could help.
The database I'm setting up is a task register datebase, it will be
used to create work schedules for workers in a metal work shop, based
on the tasks required to make a part and the tasks required to make
the subcomponents of the part.
|
by: fkealty |
last post by:
I'm attempting to make a small user database more efficient. I have an
employee table with id numbers, last name first name title and
address.
Using a combo box I select the last name and have used the following
to automatically fill in values on the form:
=. column(2) and so on for each field on the form
This works great on the form but the only entry made on the table is
the "last name", everything else appears blank.
|
by: cyoung311 |
last post by:
I'm trying to do an automatic update of one table from another through a
form. Basically, when a selection is made for an item, in this case a
particular workshop, I want to get the associated price of that workshop
from the Workshop table and update my ScheduleDetails table. The tables are
as follows:
tblStudent
StudentID
LastName
FirstName
|
by: vorley99 |
last post by:
I am trying to write some VB to copy the field names from one table in
a db to a field in another table in the same database i.e., so they
appear as a set of records.
Any clue how to achieve this?
Many thanks
| |
by: Dreamerw7 |
last post by:
Hi,
I know this is probably a dumb question, but here goes:
I have 3 tables:
REGION
REG_ID
REGION
|
by: Forest14 |
last post by:
Hello! Happy Christmas/holidays to you all
I have this huge table named "Positions" with more than 160 fields of which the fields are named with non obvious abbreviations.
I have another table named "definitions" which has the column name and definition fields.
In order to help me all along my forthcoming developments, I would like to use these definitions from table "definitions" into "Positions". Of course i can manually copy each...
|
by: underground |
last post by:
I wonder if possible on page load to query the value of a specific colmn and insert the result into another table. My query looks like so
<?
include("include/session.php");
?>
<?
$usr = "xxxx";
$pwd = "xxxxx";
$db = "xxxxx";
|
by: boss1 |
last post by:
hi all,
i m new in php.i have a problem that is
i m using a php form where i can retrieve one field value from one table in oracle db by ajax and its working properly. but i need to insert the retrieved field value into another table from the same form.i m using session but not working.
how can i do this?can anyone please solve it for me.
Thanks.
|
by: dbdb |
last post by:
hi...
i have a text box in the form, then i want the data is insert into another table.
thank you.
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |