I hope you don’t mind my using this forum as a learning tool. I am running an Access 2007 database. I am trying to create a query from 3 tables:
TblTrainingPrograms containing the following fields
Program Number (unique value)
Program Title
Program Date
TblInstructors with these fields
InstructorName, address, etc.
TblInstructorProgram with the fields
InstuctorID
ProgramNumber(linking this instructor to a particular training)
InstructorNumber(in the case of multiple instructors for the same training, the user may add new records and enter “1”, “2” or “3” here)
I need to run a query that will display even multiple instructor programs in one record, so that Instructor# 2 will appear in another field in the same record rather than having 2 or three records displayed in the datasheet view. I’ve tried several combinations, but when there is only one instructor, the Program does not appear in the query results.
I don’t really know a lot about SQL, but I’m hoping there might be a way to do this that way. Any help would be appreciated.
Hi, i am wondering how to reduce the size of access. i have included pictures within forms which obviously made the database bigger (it is currently around 12mb). However i have tried to delete the pictures to reduce the size, this method did not work at all. The database remains the same size. does anyone have any suggestions on how to solve this problem i am having?
Hey everyone! Hopefully, someone can help me with this. I've searched the forum and can't find anything on it.
I want to take a number and reduce the number of decimal places from 4 to 3 WITHOUT rounding it. Just chop off that 4th number...I don't care about it.
Hi I want reduce one minute from my date field. My date field looks like this: 17.3.2006 3:57:00 and I want it to look like this:17.3.2006 3:55:00. I have tried to make it like this: DATEADD( minutes, -2, [StartTime]) AS NewStart. StartTime is date field. But this doesent work. Could somebody tell me how to reduce one minute from date field??
How do you update a table by reducing a number by 10?
My assignment question is:
10 students have left GY101. Write an SQL UPDATE statement to reduce the class size by 10 for all modules taken by GY101 students.
I can display the students who take GY101 with the following code
SELECT moduleCode, classSize FROM ROOM_BOOKING1 WHERE moduleCode IN (SELECT modCode FROM STUDENT_REG1 WHERE sID IN (SELECT id FROM STUDENT WHERE courseCode = "GY101"));
I'm trying to create a database that is going to be used to deliver some work to several of our users. Each time they open a specific form they'll be delivered a job.
Now it works like this, the user gets an ID_JOB from queue
In the form they get all the all the work orders with that ID_JOB, the thing is i'm getting users with same duplicate orders cause i can't update the locked efficiently.
Regarding the users, the database is split, multi-user, with >30 simultaneously
I'm been trying to use dao.recordset, with transactions to try and reduce the duplicate orders.
I am new to Access and am trying to reduce a dataset I am working with to make it managable for Excel. I have three columns which have three unique parameters and one column with numberical content.
Column A Column B Column C Column D NJ Red Monday 10 NJ Red Tuesday 20 NJ Yellow Wednesday 30 NY Red Monday 35 NY Green Tuesday 40 NY Green Wednesday 60
I want to elimiate column C from the dataset, and sum column D for every unique combination of Column A & Column B. I have figured out how to reduce down the dataset to get a list of all unique combinations of Column A&B,what formula I can use in a query to sum column D on my table for every unique combination of column A&B. This is a simple SUMIF in excel, but my dataset is too large for excel.
I have created a front end and put into production for my users but I want to reduce the amount of menu bar options they see so someone cant click on a button wondering what it does and mess something up. What is the best way to reduce the amount of options a user sees on the menu bars when they open the front end of the db?
I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
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.
Here is the situation that I'm hoping that someone can help me with. I'm working with a database that tracks our condo units - from prospect coming into the system until we close them as a buyer. All the units are setup in the system so a salesperson will select from the units available. All that works fine when I create reports. The problem is trying to get the parking and storage on the same reports with the unit information. The problem is that there are multiple parking/storage units "attached" to a single unit and I cannot figure out how to get them to all appear on a single row of the report. As an example -
I have units A, B, C Parking units p1,p2,p3,p4,p5,p6 Storage units s1,s2,s3.
Unit A uses parking units p1, p2, p5 and storage unit s2.
Unit B uses parking unit p3 and storage unit s1.
Unit C uses parking unit p4, p6 and storage unit s3.
How do I write a query/report that would show:
Unit Parking Storage Unit A p1, p2, p5 s2 Unit B p3 s1 Unit C p4,p6 s3
I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
I have a database with a table with company names, then a relationship to another table that shows that companies' address, but I also want there to be an address 2 and 3 and so on, and some of our companies have multiple sites.So what Im asking is how would you be able to show multiple data, on a sing record.
I have a DB where you there's 5 tables all linked together by one project ID
tables below
Project , Staff, Asset, allowances, travel, mark up
What I can do is create a new project, then add records to each of the other tables on what different items I require,
i.e. I create a new project - called project one, in the project table I create a record stating, name, time scale, client and location, then I add different records to each of the other tables on what I require all linked to the same project ID. (probably not explained that too well)
Now I want to create a query that lists all the requirements one after the other this will make it easier to create reports and to calculate costing's.
At the moment I have made 5 different queries listing all the data, then have one report containing 5 sub reports to display the data, no this does work.
I am trying to manage a contract price from month to month. Every month, some portion (or none) of the total contract will be completed. I have a form in which the user enters 'Amt Completed this Pay Period.' Then the 'Total Completed To Date' is automatically calculated by adding the 'Amt Completed this Pay Period' to the 'Previously Completed Amt.' I would then like to use the new 'Total Completed to Date' as the next months 'Previously Completed Amt.'
Basically, I am trying to calculate a value in a form for one entry, and then passing that value to another entry to use. Does anyone know how I would pass this info along?
I'm doing a Schedule Adherence Report in Access. One table has the schedule start time and stop time. The second table has exception times (lunches, breaks, etc). Both tables have the common denominator of a unique Schedule ID. How do I bring them together with repetitive Schedule records from the first table. What I get is the following:
ID Code Time Code Time 1 Open 12:00 Break 2:00 1 Open 12:00 Lunch 4:00 1 Open 12:00 Break 6:00
What I want is the following:
ID Code Time Code2 Time Code3 Time Code4 Time 1 Open 12:00 Break 2:00 Lunch 4:00 Break 6:00
I have drafted a simple VBA code in a form button that takes the result calculated in a query and pastes the result into a table. I have attached my test database to illistrate..In my attached database, i have a list of 5 records, i am wanting to push the calculation button have all the records updated with the result that is calculated in the query in 1 go.
As it stands right now i have to do it one at a time per record, which is not preferable when there are potentially hundreds or thousands of lines. I am wanting to do this because i want to reduce my reliance on calculated cells in forms and reports where i have to draft long formulae across 3 separate queries to get the result i am looking for.
I'm building a database for a realtor friend. Part of his job is keeping track of where his clients want to live. I have added a field named "Areas". I need to populate that field with names of cities where his clients want to buy thier house. Sometimes there are only a couple of cities. Other times there could be more then 10. I don't want him to type these cities in. He is not a good typer, either am I, and he is prone to abbreviations and typos. Garbage in garbage out. I would like to provide him a drop down list, or something like that, of all the cities or areas and have him select each area and then either hit a command button or copy/paste it to that text box. Either way will work. The command button would be nifty. The result would give him the option of doing a form filter and being able to filter that text box for ex: "atlanta" and "syracuse". He then could cue these people when he has a property come available in either one of those cities.
I DON'T understand VBA code. I don't know how to write it or where to put it.
I tried to search this site and I could not find any threads like this, to my amazement. If there is a similiar post out there and I could not find it I apologize in advance.
I need a little help with a new function in our CRM db (Ac2000).
I've made a system for handling sale-prospects. When a sale is done, I want to transfer the costumer info to the costumer table. All this is well, no problems with this.
BUT, each prospect (And costumer) has contacts. There can be several contacts to each prospect, connected with a prospect number.
The challange is to transfer the contacts as well. And assigning the new costumernumber..
Dont bother going techincal right away, but a point in the right direction would be great!
how do i delete multiple records in a table, for example if i have a table that as serial_num 123 twice, how do i delete the two records, basically i don't want to delete one record, i want to delete the two records. Thanks.
