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"...
I'm trying to obtain all of the information in a text field for a query.
The user entered information and used a return (enter key) to enter information on a new line within the same field. The information after the return is not captured in the field in the query. Is there anyway I can capture all of this info?
I have 2 formulas that work for me in excel. I hope to be able to replicate the result in Access.
1) I have the following in a field called PROBLEM STORE NAME: SALE (DAY 1 - HD) Mt DRUITT (WOWPOS) (E) THE MALL (WOWPOS) TOWN HALL (RF) LIVINGSTON
I want these to be grouped as WOWPOS or ISS460 (if not WOWPOS), so return in field STORE TYPE as: PROBLEM STORE NAME.............................STORE TYPE SALE (DAY 1 - HD)............................................... ..ISS Mt DRUITT (WOWPOS) (E)......................................WOWPOS THE MALL (WOWPOS).......................................... ...WOWPOS TOWN HALL (RF).............................................. ......ISS LIVINGSTON........................................ ..................ISS I use the following formula in Excel to achieve this when I export query results from Access: =IF(ISNUMBER(SEARCH("wowpos",K2)),"WOW","ISS460")
2) I have a field VENDOR ID that may or may not contain ANY detail. If the cell is empty I need it to return N/A. If populated I need to return VENDOR CALL. The following formula achieves that for me in Excel: =IF(ISBLANK(X2),"NO","VENDOR CALL")
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.
So an employee in this database can have many different positions. The tables are linked using an EmployeeID field.
I want to construct a query that will list each employee and the last job entered for them in the database. Right now my query simply returns all the positions held (where there are more than one)?
I'm currently busy with something for my thesis as a student and I need to use Access for this. I'm not too new at access, I know how to do the very basics, let's say on the level of [if field contains *"text*", return x].
However I am struggling right now on something that shouldn't be too hard... I could do it immediately in Excel if there werent millions of rows..I have 2 tables. Table 1 regards a list of patent publication numbers (eg. WO2012024604A3) and additional data (publication date, title, etc), only the publication number matters for me now.
Now what I'd like to do is to create a third table which has for each of [Table 1].[Publication number]:
Column 2) A count of how many times the [Table 1].[Publication number] is found in [Table 2].[Cited Refs - Patent] ...
Column 3) In case a patent is cited more than once, return the [table 2].[publication Number Citing Patents] value of the earliest citing patent (so with the lowest Publication Date value).
For Column 2 I had expected it to be an easy count(iif( [Table 2].[Cited Refs - Patent] = "*"&[Table 1].[publication number]&"*")) command but apparently it's harder than that..
(1) a query that returns the latest record (newest) in a table called 'Invoices' and then
(2) places this value in a form called 'FrmInputInvoices' as the default value when the form opens. Newest record is by Autonumber and the table defaults this to top of table as views newest down to oldest.
Re (1) Query is called 'QInvoices'; the values I want to return in my query is ID (my autonumber) and Invoice_No . Must be a simple answer to put in the criteria, but I can't find this.
Re(2) What code do I use in my Form field named 'Invoice_No when the curser defaults there on opening?
I have a form that contains a combobox. I want the default value for this combobox to be the Staff_ID of the first forename to appear in a table when sorted alphabetically.
The table I'm querying is called 'Staff'. Fields within are 'Staff_ID', 'Forename', 'Surname'. The table is sorted by 'Forename' in ascending order.
From the example tables below I would expect to return a value of '3' for example 1 and '9' for example 2.
Example 1 Staff_ID Forename Surname 3 Andrew Banks 7 John Jacobs 2 Mark Jones 8 David Smith
Example 2 Staff_ID Forename Surname 9 Alice Jones 3 Andrew Banks 7 John Jacobs 2 Mark Jones 8 David Smith
I have 3 tables joined as attached and Im trying to use the PO from the PO_Detail table to display the Description from the Material_Req Table. The two values are linked as the description in the Material_Req Table is for the PO in the PO_Detail table but I just cant get the results to show this.
I have a table with duplicate records (which is ok) and I want to return distinct data for each client.
It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.
I.E
SELECT DISTINCT tblClient.ClientNo, tblClient.Name FROM tblClient
Works fine with only the Distinct records for each client returned
However
SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue FROM tblClient
Now returns Duplicates!
Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
I have a single table with customer information, one of the fields is a date field "LastContacted".
I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.
I have written this using Nz so that it can still return results if the search boxes are left blank:
Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)
This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?
I have tried:
like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
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
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
Ok, here's the scoop. I have a tournament / player score tracking database that we are developing.
1. I have a form that I would like to use to select a table number. I use a combo box to select this table number. This part is easy.
2. Once I have selected the table number, I would like to have two sets of fields automatically filled with information from two different queries (ie: player #1 and player #2). Each set of fields will include the player name and the appropriate score fields for the event. The majority of the time the data that will show up will only be the player name since we will be receiving scores and entering the information as it comes in. However, this same form could be used to edit player scores.
3. I would like to be able to tab from one field to the next when entering the information. After all of the information is entered, I would like to then hit a button that will write the information back to the two queries.
The problem that I seem to have is trying to enter information from two queries on the same form. Alas, I have been experimenting with unbound forms, subforms and unbound text fields, but have not been able to get anything to work. Would anyone have some suggestions? Any help is greatly appreciated.
When you open the form, it asks for a tool # (which will be a drop-down box). There will be a command button that will bring up a sub-form. In the subform, there are 2 combo boxes. One picks the group that the tool falls under, and the other has the section of the group for the tool.
When the user picks the section, I need it to filter the questions that pertain to that section (which is listed in a list table), and pull those questions, and populate the master table with them. (i.e., Pulls "where is tool?" from tblQuestions, and puts it in the tblQuestionMaster when section is selected from combobox). :confused:
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
Okay I have 2 tables and I used queries to filter and delete the info off those tables. Now I combined the 2 tables by using a query which is query 1. What I found was a few cells that are blank cells.
I tried creating another query based on query 1 from the 2 tables to try and delete it but it won't work. I tried everything and nothing seems to work. I can't filter the data since I am creating another query called query 3 to display the final results.
I am trying to update an existing table in Access with current information that I have imported into Access from excel into a separate table. I was able to use an unmatching query and append all records that were not already in the original table. But there are records in the update table that match the original table, and there in information in fields within that records have new information.I need to find the records that match in the two tables and update the missing data.I am able to create a query that displays the matching records but when I run it as an update query the information in not entered into the original table.
and I am trying to create a query which lists each site and the USID with the latest date in its ConstructionDate field (USID and ConstructionDate are both in tblUSID). If I omit the USID from the query and run a totals with MAX on the ConstructionDate field, I return what I would like to, but I want to add the PK (USID) of the record with the latest ConstructionDate for another query, and every way I attempt it the query returns multiple records per SiteID.
