I would consider myself a novice to almost intermediate with access.
Anyways what I need to do seems pretty simple. I just need to make a web page that pulls values from a table we have stored on our server. The users WILL NOT need to modify the info, they simple need to view it.
I want it set up so that when users modify the table locally here at our company, people from another site can view the changes on the internet...
I'm attempting to build a simple database for a martial arts tournament registration. The weight division (Fin, Fly, Heavy, etc.) depends on the age, the sex and the weight. I've tried this several different ways, most recently by using one table called "competitors" with all the entrant's information, and several other tables based on the age class/sex (i.e. I have a table called Age 8-9 Male with the weights and divisions in it, and other tables for the other ages/sex). So, basically, what I'd like to have happen is a report that will lookup in the correct table the division, based on the weight entered. In other words, for a record where the age is 8-9 and the sex is Male, the report compares the weight to the values in the 8-9 Male table, or if the record is a 12-13 Female, it looks in the 12-13 Female table for the weight, and places the correct division in the Division field of the report. Probably going about this wrong.....but any help would be appreciated.
I have a textbox & button that when entered & pushed - run a query for the top values randomly depending on the value entered into the textbox.
My code is...
strSQL = "SELECT TOP " & Me.Text140.Value & " [CAN - NAME].Name, [CAN - CPT/VOUCHER].Voucher_Number, " & _ "[CAN - CPT/VOUCHER].Procedure_Code, [CAN - CPT/VOUCHER].Service_Date_From, [CAN - CPT/VOUCHER].Patient_ID, [CAN - CPT/VOUCHER].service_id, Rnd([service_id]) AS RandomNum " & _ "FROM [CAN - CPT/VOUCHER], [CAN - NAME] WHERE [CAN - CPT/VOUCHER].Service_Date_From Between " & Me.StartDate.Value & " And " & Me.EndDate.Value & " ORDER BY Rnd([service_id]) DESC "
It runs like it should but doesn't populate any records...that is until I go into the design view, deselect one of the fields and then reselect the same one - only then does it populate.
Do I need to open the query first and then update/append instead?
I trying to build a usage tracker. Users will take a reading once a week and enter data. I want to open the form and have 2 fields filled in based off the last reading entered into the table. I have an ID field (autonumber) and I tried using that field -1 to populate my other fields but it does not work. any suggestions
I'm querying off of a teradata table in Access. I have the teradata table linked to a table I made in Access which will only pull specific cars(example). How can I make the query pull.. like car* for every record I have in the table?For example..My made table has20120220321Q24Retc...the teradata table shows this201G101201G102201P202203A12021QP131the record means ...the first 3 characters is the car name, the middle two char is the location, and the last 2 is the day of the week.Btw in the query I use in my other querying system it has an option "Begins With" which works, I don't know if acces has this? Can anyone assist me on this? If I am unclear let me know!!
I have created a database to track the paper files that I am accountable for. Every day I get requests from workers for various files. I have split the database so that multiple users can access the db and edit or change the status of files. In the be I have the main db with fields required for the files and I have another database (table 2) with similar fields that i use to generate an email form that workers fill out to request files. Access then automatically pulls that data from the email form and inserts in to table 2. I pull those requested files then enter the data in to table 1 so that I know the who/what/where of a files location. I want to be able to automatically insert info from table 2 into table one after I have pulled the file and checked it out.
I have two tables; one has just variances entered, the other is the total number of events for the month (normal events + variances). The variances become the numerator, and the total events is the denominator.The report does counts of variances based on location, type of variance, etc.This is working just fine, but I also need to show on the report the number of variance/total events (%).
I am struggling with pulling the data for total events into the variance report.The variances all have dates, the totals have start and end dates (monthly). For the variances, my query asks for date range, but I want this same date range to be used for pulling in totals from the other table.I am trying to use DSUM("monthlytotal", "tblTOTALS",Between "StartDate=input start date from report query" And "input end date from report query").Should I setup a form that after the start/end dates are enter, the report is launched using these dates?
I am trying to figure out a way to pull a date from a table and add it to another table automatically. I'm building a database for a harvest/trucking operation and we want to be to set the date everyday and then it pull that date on all of our tickets, reports, etc. Most days we run till after midnight but we want to still record the date as the day we started so I don't think pulling the date from the computer itself would be a good option.
Ok, I have 3 tables. One lists a contact in conjunction with the branch of the company and the trips that contact takes. The second lists a contact in conjunction with the branch of the company and which team they work with (may work with many). The third should list their email address and their phone number. Is it possible to pull the contact name and branch of company from tables 1 and 2 into 3 automatically, such that all I have to input into table 3 is additional contact information? If that is possible, is it also possible to only pull each contact/branch of company pairing once (I don't want four entries for Joe Shmoe/Sales, even if he's taken 4 trips)?
Table 1 Branch of Company Trip Dates Trip Location Contact
Sales 4/1/14-4/12/14 Chicago Joe Shmoe
HR 6/2/13-6/4/13 New York Jane Doe
Table 2 Branch of Company Company Team Contact
Sales Blue Devils John Deere
Sales Jets John Deere
Sales Jets Joe Shmoe
HR Sharks Jane Doe
Table 3Contact Branch of Company Email Phone
Jane Doe HR jane.doe@company.com 800-555-1234
Joe Shmoe Sales joe.shmoe@company.com 800-555-1235
John Deere Sales john.deere@company.com 800-555-1236
Trying to create a simple data entry form to get information and populate fields from another table. i.e. type in info in one field and the other fields are displayed in the form with information from another table.
I have a contributor tracking table that is linked to a form of the same name. I created a make table from a query that calculates the total to date for each contributor (based on their contributor ID in the tracking table). I want to place this sum to date, in read only mode, on each contribution record for each contributor in the tracking table and on each master record in another table with the contact information for each contributor.
The contact table is in the one and the contributor tracking table is the many. If this isn't clear, I can upload the database. I essentially want to link a field from one table to a form with a different table source. The sum to date should only show for the record with a matching contributor ID.
Table 1: Account Number Start Date End Date Cost data** Budget data**
Table 2 (Imported excel file with cost/budget data): Account Number Cost data** Budget data**
Table 1 is the main table that will be viewable in this database. The idea here is that new Account Numbers can be added to Table 1 throughout the year. It then pulls the cost/budget data into Table 1 based on the matching Account Number between table 1 and 2.
So, if the Account Number (Table 1) = Account Number (Table 2) then it pulls the cost/budget data into the cells on that row. I am trying to make this automated since this data is updated weekly and imported into Table 2 from excel.
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN) SET TableA.HCC = TableB.HCC WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL ORDER BY TableB.HCC, TableA.CDN;
There is a text box in a form that user can enter the value. How can I check if the value entered by user is already existed in the record of the linked table or not? If the value (or record) do not exist, that new value/record will be added to the table. If the value do exist, just show the msgbox to indicate.
I have a table (Table 1 let's say) which contains a list of numbers that all have unique ID numbers in another table (Table 2). Is there a way to create a column in Table 1 that will link those unique ID numbers from Table 2 to their corresponding values in Table 1?
What happened was I imported a list from Excel and there are frame numbers which are unique (but I still gave them an ID) and I now realize that I have another table which lists all these products, which all contain this frame number. I would like to replace the frame number with the correct ID number.
I have a database that has 2 tables. Table A and Table B. Table A is my primary table. On this table I have 2 fields. The first field is a LOOKUP Field that looks up information from Table B and displays my selection in the field on Table A. Then using DLOOKUP I automatically input the information in the Second Field on Table A based upon the selection from the First Field.
This is working mostly correctly. However, the problem is, when I click on the next record in the table, it automatically changes the Second Field on that record to the same value as the record before it and continues this trend each time I click on another record. This occurs without me making a selection in the first field. If I make a selection in the first field it does change the Second Field to the Correct Value, but then the next Record has the same issue.
How do I go about fixing this so it doesn't change the value with the change of the record. Only change if I change that particular field within that 1 record?is there a way to restrict the Value's in my lookup field to only include the Values from Table B that aren't already in Table A?
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
The attached Access XP file demonstrates my problem. I've included a form to make testing easier.
Each record in the Projects table has one or more linked entries in the Keywordlink table, showing keywords that apply to that record. Each record in Projects has a Yes/No 'Utility' field.
A third table, Keywords, supplies the keywords that the user can apply to records in Projects, using the subform on the main form. The Keywords table also includes a True/False 'Utility' field. I have set this to True for for the first three keywords.
I need a query, a series of queries or some VB code that updates Projects_Utility for all records to True if and only if the record's linked entries in Keywordlink include all of the keywords for which Keywords_Utility is True (a boolean 'and', as opposed to an 'or'). Otherwise, Projects_Utility must be set to False.
In the attached file, with the current settings in the Keyword table, the 'GetSelectedProjects' query should then produce single-row listings for ClientA and ClientF.
I'd appreciate any help you can give me on this. I'm not a programmer, but I can manage a bit of VB code if I have to.
Situation: 3 tables. Manufacturers, Countries & Provinces/States. 1 Form for data entry in Manufacturers.
Countries table contains ID, Country and CountryCode fields. i.e. 47, Great Britain & GB
Provinces/States table contains ID, Province/State and CountryCode. as in 1, Alaska and US.
In the form the country is easily selected from a list refering directly to the Countries table.
Problem: How to make a list in the form from which the user can simply select the province for the country that has previously been selected. And not a list with all teh provinces and states from every country in the world. (This would be a really really long list...)
Hi, I have a problem, I have a table were I list networkservices and their logical ports and I have another table were I list IPaddresses used by different machines. I'm using a multivalued lookupfield to pick which services I'm using for each Ipaddress, and thats working fine, but now I want to make the ports which is listed in another column, to automatically show in it's own column in the IPaddresses table depending on which services I'm choosing for the different IPaddresses. Is this possible?, and if it is how can I make this happen?
Is it possible to have the field names in a table be set by the values in another table? The desire being that for a database used in various locations, the local variations could be changed in one table which would then propagate that change throughout all the forms, reports, tables etc.
I have two tables (Table 1, Table 2). Table 1 is blank and has the fields "Field1, Field2, and Field3." Table 2 has data and 10 different fields ("Field1, Field2", etc). I want to copy all data from the matching fields in Table 2 to the corresponding Table 1. I've tried an Update Query in design view. Did not join any records, and just a simple:
Field: Field 1 Table: Table 1 Update To: [Table2]![Field5]
This returns 0 records to update. I thought about a Make Table to override the entire Table, but that isn't working either.
I have a table (tblConversions) that I'm using as a base for replacing values in a different table. tblConversions is set up as such and is made up of nearly 100 records:
ID LABEL CODE 1 Ashaway ASH 2 Barrington BAR 3 Bristol BRI 4 Jesse Smith BUR ....
Another table (tblSysItemLoc) has nearly 1,000 records with a field for Location Names such as Ashaway New Books, Ashaway Fiction, Ashaway Non-Fiction, Barrington DVDs, Barrington Reference, etc. Other fields in this table are just statistics.
What I need to do is loop through tblConversions, and find in tblSysItemLoc where the Location field Starts With the value from tblConversion.LABEL and replace the Entire field with the CODE. For example, from tblSysItemLoc "Ashaway New Books" gets changed to "ASH", "Barrington Reference" gets changed to "BAR", etc.
I feel like I need a loop inside of a loop, but I'm not sure where to begin. Loops are not my specialty.
