Right, my first access database is coming along well, with the help of some people on here, but I have another question. The database is designed to track jobs in our small workshop.
We have a 'price matrix', that has our regular customers on the left, the material type long the top ( mild steel, brass, etc), and a cost in each area for each of four types of standard job, so i suppose its a sort of a 'cube' rather than a matrix, except not all customers and materials have an agreed price for each of the standard jobs (there are only four of them, a maximum of 3 per customer)
Now, my database revolves around a 'main' table, with a unique order ID, customer name (from lookup table ), and other details. Initially, I have created another 'cost matrix' table, with a primary key, customer ID (based on the same lookup table), material type and job type (both lookups), and price fields. What I want to do is that my guys can select material and job type, and the cost will be recorded somewhere. Now, I understand that you shouldn't have the same data twice anywhere in the database, so I figured of I am recording the material and job type in question on the main table, and given the customer ID is recorded as well, I should be able to reference this to my cost matrix table and then be able to populate a field on the main form, query against it on reports that sort of thing. However, there is a field on my main table for cost adjustments ( say we work 24 / 7 on a rush job), which is manually entered on a job by job basis, so should I record the total job cost in my main table, or always calculate it when needed using a unit cost + adjustment type of calculation?
Also, how do I populate the field on the main form that the cost should be 'x' from the'cost matrix' table, given the values of the customer, material type and job type fileds?
I have a form with fields that gray out or disable depending on selections. If you make selections that result in disabled fields and then save/close it, how do you ensure the fields remain grayed out/disabled when you reopen it?
I have 2 fields on my form service interval combo box and vehicle mileage text box I m trying to create a pop up message on there values
if service interval is 12,000 and the mileage entered in the vehicle mileage is over 12,000 then show pop up message.
this works but I'm sure its wrong don't no why I need the -1 anyways here's what I have
Code: If Me.Vehicle_Mileage.Value > Me.Cboserviceinterval.Column(1) - 1 Then msgbox "test" end if
Now my problem what I can't get it to work . I still want the pop up message, if say the service interval is 12,000 and mileage entered in the vehicle mileage is 1,000 miles below the service interval I still want the same pop message but if 2,000 or more below service interval then no pop up message .
I have tried a number of sequences with no avail....
I would like to only display certain fields on a form depending on a criteria in another cell. For example if some enters 4 in 'Schemes' it would display 4 data entry boxes scheme1, scheme2, scheme3 etc - is it possible?
I am in the of designing an access 2010 database for data entry. Is it possible to create a button on a form in which a prompt asks a user for which records to export. Then depending on the entry export specific fields (First Name, Last Name, DOB) to a specific excel 2010 workbook (setup) and worksheet (template)?
For example, if the user entry is 1, only record 1: First Name, Last Name, DOB is exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx). However if 2,3,4 are entered then records,2,3,4: First Name, Last Name, DOB are exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx).
I am trying to write a query that deletes all records from one table that meet the criteria of another.
The table i need to delete from is called StartingPoint and the other table is called R7e, both tables have a field called "Reference" and R7e has a field called "Change".
I have been stuggleing with various different delete query's to no avail.
So i need the query to delete a record from StartingPoint where [R7e].[Change] = "Suspended". The reference field is a primary key, this field will need to be used to determine which records to delete from StartingPoint.
What i need:I have the attached database which includes 2 tables (Table1 and Table2) which are related One-2-Many (Table1 is the One and Table2 the Many)There is one form also with the name "Form1" which includes 11 unbound (ctr1,2,3...11) and are calculated with the values of fields [MainDate] , [Days] and [EndDays]
The Issue: As you can see in the attached sample, on Table1 i can assign the "MainDate" value , the "Days" and on field "EndDays" i can have the value of the [MainDate]+[Days].
I need to update the field "RefNo" on Table1 on depending the date range of the field [EndDays] and the controls [ctr1-11]. Specifically i need : If the Date() is in the range of eg. [EndDate] and [ctr1] i need to have the value of the first record of the Table2 (RefNo) on field "RefNo" into Table1. If the Date() is the range of [EndDate] and [ctr2] i need the second record values of the field [RefNo] into Table2 and so over..
I have a table (tbloutput) which has details of customers and which staff they have been contacted by.
What i want to do is, export the details from this table into an excel sheet using a template that i have set.
What i want to do is create multiple excel outputs using this template depending on the name of the staff. So each staff will have a seperate workbook which was created using that template. And i also want the new workbook to be named for that staff member.
So in short
Table exported to excel workbook and excel workbook named : Blabla staffname.xlsm
I need to add new records to a table depending on what the cbo box displays. I figure this is the way to do it but I it's not working, I have only writen the first select,
Private Sub Comando6_Click() Dim dbPalavraChave As DAO.Database Dim rstchaveCFEPC As DAO.Recordset Set dbPalavraChave = CurrentDb Set rstchaveCFEPC = dbPalavraChave.OpenRecordset("chaveCFEPC")
i have a standard database with tblProducts, order, employees, customers, suppliers etc. i also have a table called tblperson with personID which acts as a one, with the many in three different tables (customers, suppliers and employee) the person table holds data such as first name last name address city postcode and phone. now, ive created a form called frmPerson that acts as a adding data to my database. ive got a load of buttons a working add, delete and a save button, i also have the option to switch through records next, last, end and first. now basically in this form i want to be able to add data i.e. fill out the text boxes and then have a combo box or something similiar that when i add the data i can basically click an option the says 'employee' 'supplier' or 'customer' and then the data gets added to my person table however the difference is that it will also be associated to the record in the wanted table.
Every quarter I run a report that pulls loans that meet specific criteria.I export this report into excel (the loans fall into column A)I add a file number and box number in columns B and C.I import the excel spreadsheet to table 2 (they're linked so I don't need to import, it's automatic)
Now that I have the updated information back into the database (table 2), how can I get this information back into table 1? The excel spreadsheet only contains a few loans that need to be updated in table 1. I have tried creating an update query with both tables linked and use the "update to" field. However, when I tried to run the query, it says I have 0 records updated.
My update query is as follows:
Field: Access Bar Code Table: Table 1 Update To: [Table 2].[Access Bar Code]
Field: Access Box Number Table: Table 1 Update To: [Table 2].[Access Box Number]
Basically I'm trying to have the query update specific fields in table 1 based on the information from table 2.
I don't have any database experience whatsoever so please go easy. I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device". The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
I created a form in Access that retrieves data from a table. Inside the form, I am able to access/populate data fields with data from the table. I also have data fields, inside the form, that requires key-in data. I have some how lost the ability send all data field information to a second table and clear existing data fields for new entry.
Questions: What settings, code or buttons can I use to send/store data field information to a new table? What settings, code or buttons can I use to automatically clear all data fields from my form once data has been sent to the new table?
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code: SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay FROM [Tble-wcDelays] GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
I have a database that will register the emails coming in and what time, also the time, date out and person.
I have a form with the fields to be filled in and a submit button. There are some fields that are automatically filled in and others need manually fill in.
below that part is a sheet (subform in the form of sheet) that should be filled in with the above data. Once it is there, it should empty the fields so they are ready to be filled in again. If I fill new data and press the submit button, it should go to the next available row.
I have a copy of the back-end that gets a search key error 3709 on two records. In other words, I can duplicate the problem.
The interesting part is that I can update any other field on both these records and save the record, but when I try to change two specific fields, I get a Search Key Error and have to ESC out to continue (basically UNDO the change). Both fields are text fields with lengths of 7 characters and 255 characters, and both are COMBO Boxes on the form.
I tried to focus on the form think there was an issue in the code. I can definitely TRAP the 3709 error on the ON ERROR event on the form using "if dataerr = 3709", but then I tried something even simplier.
I went directly to the table and to each of the records. Again I can update any other field in the record but these two specific fields. When I try to change either of them and move to another record, you get a Search Key Error 3709.
By going to the table record directly I'm as low level as I can get. There are no validation rules on either field at the database level. If it was truly CORRUPT would it let me update any of the other fields on either of these records? One is an empid (not a primary key but is indexed with duplicates okay and not required), and one is status code (not a primary key but is indexed with duplicates okay and not required) so they're no critical fields, but something is keeping them from CHANGING.
Just tried something else; deleted the INDEXES on both the fields. Now it works! I am completely confused now because it really wasn't a corrupt record, but the indexes are causing the problem. Do I need to update the indexes somehow when the users selects a new empid or status code?
I have a product table that includes the name of the product, the quantity ordered, and the unit price. This is a summary table for the whole year showing all of the orders. The Primary Key is [ID] from the [tblOrders] table. I attached a screen shot of my query. I don't know if you can see it. I want to generate a report where each product is listed once, the unit price for that product is listed, the total number of that item that was ordered throughout the year, and finally, the total amount spent for each item during the year.
I have come up with another one that may be easy for you.
I have a form that has a list box with two fields in the table. Call the table: STATE TABLE1 Call the first field: STATE NUMBER 1 Call the next field: STATE NAME 1
When I access the list box on the form, I have it so I can see the States Name(STATE NAME1) and I click on it and the (STATE NUMBER 1) is place in another table called: INVENTORY2 in the STATE#2 field. That works all well and good.
I would like to be able to continue this, at the same time I select the (STATE NAME 1) on the list box. I also want to place (STATE NAME 1) into the INVENTORY2 TABLE in another field call (STATE NAME2)
I was going to write an expression to do this but had trouble with finding the STATE NAME 1 field.
I am sure there is an easier way.
PS This table is being used in another application and there is no way to modify any of its field at the time of the transfer. All the fields need to be populated at the time of the transfer.
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.
Anyway, I would call myself intermediate level at best with Access. I never expected to have to do so much with it, but when my bosses found out I could do Access basics, they began demanding more and more.
I manage an EMR from which a datapull occurs on discharge to various access databases.
They wanted me to add a triage patient data pull to track what procedures are being done to triage patients. So I built a database with the following 2 tables.
tblTriageVisits and tblTriageProcedures tblTriageVisits has the following fields -Patient ID -Export Time
[Code].....
The tblTriageVisits stores all the patients triage visits. The other table stores what was done to each patient on those visits
It should also be noted that this EMR exports times in number of seconds since 12/31/1975, so TimeProcFMS is the number of seconds since that date for example.
The pulls work great and the duplicate record elimination method works great.
I have to design a couple of different reports based on this data and one in particular has pretty much flumoxxed me.
I need to make a report that lists each patient by date they were there and then shows what was done to them. I have tried several different variations of this as both queries and reports. The report breaks down when I try to get it to show just those procedures that would be for that triage visit. I got to the point where it would group by day and then sort the patients alphabetically. Then I added in the visit fields. And it would put all triage procedures for that patient from all their visits in each visit.
So, I was like great, let me filter out those that don't occur between the two dates, admission and discharge
I used the following two formulas (the first one on the procedure name field the second on the procedure date field)
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],[Procedures FMS])
=Switch(DateAdd("s",[TimeProcFMS],#12/31/1975#)>=[Admission DateTime OB DateTime] And DateAdd("s",[TimeProcFMS],#12/31/1975#)<=[LD Discharge Date/Time DateTime],DateAdd("s",[TimeProcFMS],#12/31/1975#))
And they work, sort of. They do eliminate those procedures that occur outside of the date range I am looking for. However, the access report still leave spaces in the report where the filtered out items would be.
Did I approach this problem correctly? And if so, how do I get rid of those spaces?
Note, I can read and mostly understand SQL, but am not comfortable working in it yet. And the Access SQL box gives me a headache, it is so jumbled up.
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
I have a table called BID with the following fields
bidder, seller, iid, starttime, bidtime, and bidprice
bidder is an id number that is reference to uid of table USER
the USER table have the following fields,
uid, uname, city, and state
I created a BID form for the user to enter data into the BID table. What I am trying to do but is having trouble with is have the user search for their name that may already be store in the uname field in the user table, and what ever they choose base on their search, the uid of this uname gets store into the bidder field of the bid table.
I still have a second step where the user in the bid form can search the iid but shows the itemname from the item table, and the value of seller, iid, and starttime from the auction table gets populated into the bid table.
iid from the auction table is reference to the itemtype and the following fields exist in the itemtype table.
I have a table "Product" and in this table I have two fields "StoragePlaceID1" and "StoragePlaceID2". Both these fields link to the look-up table "StoragePlace"
*Attached Image "Product_StoragePlace" from the Access Relationship Window"*
When I want to add a new Product from my inter face i get the error you can see in the Attached Image "Save_Error".I think the problem is that the Relationship is defined as One-to-Many,there a way to define the relationship as Zero-to-Many.
