Tables :: Processing All Records In Table
Dec 5, 2013
I'm working on a system for recording employee information, leave details, etc. I need to run a monthly update to increment each employees leave balance.
The two tables now in question are;
Employees - relevant fields are EmployeeID (key), Active (Boolean) and Leave (Numeric) [annual leave entitlement - eg 15 days]
The second table is LeaveTrans; Emprec, LDate, LType, PrevBal, ThisLeave, NewBal, Comments.
What I need to achieve;
For each "Active" record in the employee table, detirmine the monthly leave entitlement ("Leave" /12 - no problem here).
Append a new record to the LeaveTrans table with;
Emprec, LDate (Date of the update), LType (="Monthly allocation"), PrevBal (The NewBal from the last record in the table for this employee), ThisLeave (monthly allocation) and NewBal (PrevBal + ThisLeave)
I will need to access the last record for this employee to get the previous NewBal, before appending the new record.
The problem I'm having is appending to the LeaveTrans table, and then moving on to the next record in the Employee table, to repeat the process.
View Replies
ADVERTISEMENT
Nov 21, 2014
I have a table (tblInstallations) which has records of date installed (for machines).
It is calculating that "next due maintenance" is one year after installation.
Sometimes we need to visit the site to maintain or service the machine before/after the specified date in "next due maintenance" and we need this to be calculated to reflect a year after date of visit.
I have a table called "tblMaintenance" which takes information about the machine an engineer is visiting from where originally the information are kept and that is in "tblInstallations".
How can I link the new date of the visit to update the record for "next due maintenance"...
View 4 Replies
View Related
Nov 21, 2012
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
View 3 Replies
View Related
Jul 30, 2013
I have three tables. I want to combine all the records in these tables into one table. I need VBA code to do this. The first table is called down1, the second table is called down2 and the third table is called down3. All these tables contain the same fields so I don't think combining them will be a problem.
View 3 Replies
View Related
Nov 21, 2005
Hello,I'm designing a contract and payment processing database for a friends construction company. I've made an attempt at designing the data model, but wondered if anyone out there had ever come accross a similar scenario and could be of any help. I have attached a copy of my initial ERD.Entitys are as follows:CompanyHolds details of the company that are operating the system.CustomerBilling details for the customer.SiteA customer has many different sites at which work is undertaken.ContractA contract is created for all work undertaken at a customer site.PlotThe work a contract covers is carried out on various different plots at a customer site.StageWork is carried out in stages on a plot by plot basis. When a work stage is complete the stage can then be invoiced for payment.InvoiceOnce work stages are complete an invoice can be raised. An invoice can be for one or more stages (always under the same contract).Invoice_LineDetails of the different stages billed under a single invoice.PaymentStores detail of a customer payment.Payment_AllocationA single payment may be split accross multiple invoices. A single invoice may be paid through multiple payments.The main area I was hoping for help with is payment allocation, the requirements of which are listed below:1. When a payment is received it must be possible to split it accross multiple invoices (a payment will only ever be for invoices within a single contract).2. It must be possible for an invoice to be paid in part (i.e. be paid by mupltiple payments).3. If a payment isn't allocated in it's entirity, the the remainder should be put to a payment on account for allocation at a later date.4. An entire payment should be able to be put to a payment on account and then allocated later.5. It must be possible to enter credits against invoices.The reason I have Contract joined to Payment_Allocation is so that a payment on account can be created and linked back to the contract, for allocation at a later date. If a payment on account is stored as an entry in the payment_allocation table it will become difficult to then allocate the payment on account to invoices and keep an audit trail. The payment on account isn't really allocated until it is put against an invoice so I'm loathe to do it this way.On the other hand if I store the payment on account in the payment table (until allocated) it may make it harder for me to produce my statement report.I'm also having difficulty seeing how I can build a statement of account from these two tables...do I need to build up a transaction table in order to do this effectively? Or a temporary table of transations to base my report on?Any advise or pointers would be very helpful.Thanks in advance for your time.webbmatt
View 1 Replies
View Related
Dec 11, 2006
Hi
I have some code in a function that I want to run BEFORE a form (FormB) is displayed to the user.
FormB is called up by clicking on a button from another form (FormA), but when I display the ActiveForm . . .
Dim MyFormPO As Form
Set MyFormPO = Screen.ActiveForm
MsgBox "here!!-" & MyFormPO.Name
. . . I get the form name of FormA, and it errors-out because the objects I'm trying to reference do NOT exist in the Active form.
I've tried calling the function from every Form event ("OnOpen", "onLoad", "OnActivate" etc,,) but ActiveForm always seems to be the first form.
Anyone any ideas, please
Thanks
ajm
View 1 Replies
View Related
Mar 25, 2013
I've got a problem I can don't know how to solve (since I'm a complete access noob). I've got an acces table that is populated with around 1000 entires, all starting with letter N, followed by numbers. Then I've got another table from last year which is also populated with 1000+ N entries AND 1000 V entries.
I would like to trasnfer/copy all V entires from last year table into this year table. I'm sure this is possible but I don't know how. In short, I would like to copy 1000 rows of last year data into this year table/data. What's the easiest way (if there is any)?
View 3 Replies
View Related
Jan 22, 2014
I have this table "tblPreventativeMaintenance" which calculates when the next appointment for service. Stored in the field "NextDueService". I have three fields in this table. "SerialNumber", "NextDueService" & "Engineer'sReport".The whole purpose of the table above is to do the calculations.Now the results of the calculations I need them to go into another table "tblInstallations" so I can use it to show Next Due Service when there is a call out in regards to certain ready installed equipment. I have same fields in both tables.I tried update query but for some reason it didn't work or at least wasn't giving me the results I needed from it.
View 8 Replies
View Related
Oct 14, 2013
I want to build a demo db and make it so the user cant add more than 10 records. Perhaps a popup when trying to save the 11th...
View 4 Replies
View Related
Oct 21, 2013
How can i count records in a table like countif function in excel
ex.
records Count
Row 1 A 1
Row 2 A 2
Row 3 A 3
Row 4 B 1
Row 5 B 2
Row 6 A 4
View 3 Replies
View Related
Sep 22, 2014
I want to relate two records in one table, a parent child relationship. I can accomplish this task with two tables I realize. I'm wanting to write a database that will map a family tree. Internal to one table I want to be able to establish a series of relationships, parent-child, sibling, et cetra. Can this be done?
View 2 Replies
View Related
Jan 16, 2014
I have to move records from one table to another.
When we lose a client I need to take them out of my current table and put them in my dropped table.
I don't want to lose my summary info and I want the dropped client to keep it's ID Number which is an identifier.
Seems I can't just copy it from one table to another and then go back and delete it from the Current.
Seems I can't cut from Current and paste into Dropped.
In a perfect world it would be great if my boss could just check a box in Current Table and the record moved to Dropped all by itself!
View 7 Replies
View Related
May 23, 2014
What I would like to do is have about 10 users fill out an Options Group in another table (tblVote, [field Vote]) while referencing specific records in another table (tblRequests, [field ID]).
The part I can't wrap my head around, which is probably fundamental, is linking the (tblVote, [field Vote]) to (tblRequests, [field ID]). The field ID in tblRequests needs to have up to 10 separate "votes" in tblVote.
View 6 Replies
View Related
Aug 1, 2007
Is there any function to delay the system processing for a number of seconds.
For the time being I am do it indirectly by using loops, however, it is un proper way, as it is not scalable.
Also I dont want to use the timer control.
Thanks,
View 4 Replies
View Related
Apr 18, 2005
Does anyone know of any software or have any code that would allow to process credit cards.
View 4 Replies
View Related
Feb 18, 2014
I have a table with at least 13.000.000 records. There are many duplicates records... For example
ID Name Family mobile car number chassis Register_Year
1 Roy Jalbout 9999 123456/G ASF4546 2005
2 Roy Jalbout 9999 854658/G GRK554JFJD 2009
3 Tony Elishah 1234 854658/G GRK554JFJD 2012
4 Sam Markos 5478 854658/G GRK554JFJD 2014
5 Roy Jalbout 9999 123456/G ASF4546 2005
As you can see ID Number 2,3,4 have the same car but every one bought from another so it's not duplicates
The duplicates here is the ID's number 1,5. So how to remove the duplicates. I remember you i have at least 13.000.000 records. I try to make a query to find duplicates then i make a copy of the original table and than i should make a primary key then append the data from the original table to the copy table, but here i have more than one criteria
When I made a query to find duplicates the result was 680.000 records and every one have a minimum 2 duplicates an maximum 4 duplicates so it's about 2.500.000 duplicates records at least....
View 5 Replies
View Related
May 19, 2014
What I have is a database that I have done some tweaking on and in the meantime the original db has been in use which has added around 200 or so more records in the table.
What I would like to do is to just update the db that I have been working on with the older db table(the one who has the additional 200 records).
EX. DB A(Old DB, Newer Table) DB B(New DB, Older Table)
I want to put DB A table into DB B
Is this a simple fix? Or do I need to write some sort of query to update the records in the old table? I've tried to export the excel file and then import but it puts it in unrelated objects and then my switchboard or nothing works.
View 9 Replies
View Related
May 2, 2015
I am creating a task tracker where the user will enter a task which is working. If he is complete the task at that moment he will submit the task and everything is done for that task. However, if there is some issue or if the user need any information, then the user will enter a comment and will hit hold. My queryis once the user hits hold button, the table "Main" will capture the hold date and time. I need a second table, may be by name "Inbox" which will only show records kept on hold.
Secondly there should be hyperlink for "ID" of Hold records in table "Inbox" and when the user click on the the "ID" to resume the task, it should take the user to "Main" form and display that particular record (record which the user had clicked). Also, the date and time should be recorded when the user click on the hyperlink in Resume_Date and Resume_Time fields for that record.
View 5 Replies
View Related
Jul 11, 2013
We have a database which has a table with 2 fields - here is an example of the data:
Order File Path
240971 cclapps1scannerWorks Orders 2008Works Orders 2008ITL1ITL1TMSPC11.GFD
We basically need to change the file path to read cclapps3 instead of cclapps1 in every record.
View 2 Replies
View Related
Dec 9, 2012
I have a form to enter new records in a table.But when user enter empty record i want to display a validation text to to fill the required.I tried this code.
If txtItems="" then
msgbox("please fill the required fields")
else
code to insert the records.
end if
with this code its accepting the blank record also.What is the correct code?
View 1 Replies
View Related
Mar 18, 2014
Is there any way other than through coding/controls I can lock specific records in a table.
I have a reference table with less than 200 records on it. 5 and only 5 records need to be locked so that they primarily can not be deleted but also so they can't be edited.
I've read about locking it at form level but that, to me, means some sort of coding.
View 2 Replies
View Related
Sep 3, 2007
Hi all. I am considering developing a purchase order and warehouse management database, to be manage a cold store in my workplace.
What i have in mind is a system where the details of each pallet coming in is recorded (weight, quantity, location of pallet in warehouse etc.) is recorded, marked off against the corresponding purchase order number and then a label is printed with a barcode to quickly identify that pallet.
Handheld barcode scanners would be used to record when a pallet leaves the warhouse, and where its going to(despatch, production etc.). Ideally the scanners could be used to give forklift drivers order picking info aswell.
I'm fairly confident i can design the database, however, i have never done anything with barcodes, scanners or handheld devices. Is this even feasible with access? How do i go about doing it, any advice, example databases etc. greatly appreciated.
View 1 Replies
View Related
Nov 7, 2006
I have two identical databases in MS Access. I have created a query in the first mdb file, which retrieves the matching records in two tables.
My problem is that i want to execute the same query created in the database1, on database2, without creating the query in the database2.
I think it is possible, but the query i was trying was not working file.
select * from db1.[Query1] in 'C:DBDB2.mdb'
where db1 is the name of the first database,
db2 is the name of the second database and
Query1 is the name of the query in the db1.
Thanks in advance for the help.
View 1 Replies
View Related
Apr 22, 2014
I'm wondering if it's possible to create a Query / Macro / VBA or whatever works, to detect the duplicate records in a table and delete them with the push of a command button. Or, even to do this automatically when the database opens using an AutoExec macro or something ?. I'd like for one copy of each record to be left intact in the table.
View 8 Replies
View Related
Aug 21, 2014
I have a single table database for inventory. Every 3 months or so, some records get changed or updated. My manager wants to keep an archive of all of the records that have been changed so we can go back and look at an history of all of the records. So, My thought is to create an "Archive" table, appended all of the current records to it. Then, when changes are made, create an Append Query, or what ever works, to copy just the changed records from the Main to the Archive table. There are only 200 items in the inventory so it is not a large database.
View 1 Replies
View Related
Sep 23, 2014
I'm making a library database program thing... There's an option for the user to view all books on loan.
I have two tables:
Books, which has columns ID*, ISBN, Author, Title, Year, Location
BorrowerStorage, which has columns Book ID, Name, Email Address, Desk Number
Book ID in BorrowerStorage is related to the Books primary key.
Now, for the viewing all books on loan, I want it to produce a read only table which contains all the entries from the BorrowerStorage table and the corresponding Title/Author columns (i.e. the records for which the ID in Books column = BookID in Borrower Storage column)...
View 5 Replies
View Related
