Hello,
I have attached a zipped excel workbook to best describe what I'm trying to do. I have table1 and table2 and I'm trying to write a query in access to get the output as shown in the workbook. I'm having hard time getting this right. I would appreciate your help if possible. Tks
Richard
I created two tables, let's refer to them as Cars (VW, BMW and Audi) and Colours (White, Black and Grey).
Is it possible to create another table based on these tables - i.e. in the new table the rows will be the Cars and the columns the Colours as such:
White
Black
Grey
VW
BMW
Audi
And should I enter another Car or Colour in one of the first mentioned tables, then I would like this "new" table to update automatically. For example, if I have a new Car (say, Merc), then I would like the "new" table to update to the following:
White
Black
Grey
VW
BMW
Audi
Merc
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
I think what I want is:
1 table(1): record of people & contact details
1 table(2): list of events with check box's with the names of people from the other table
1 report: listing how many events people have attended.
When I add a new person to table 1 I want a field to be added to table 2 in the form of a checkbox, also when I delete this person I want this field to be deleted in table 2.how to make this an automated process.
I have a form that users can input data into and based on that data it runs a query and generates a report. These reports can be different based on user entered data on the form. My issue is within the report I would like to sum certain fields. The problem with trying to sum theses fields is that they show up on each row so I have hidden duplicates but when trying to sum the field it still trys to count the hidden duplicates thus giving a value that is of no use.
I have tried many methods to sum but one of the problems I continue to run into when I create a text box and build an equation and reference the field I would like to sum is when the report runs it is asking for a value to be entered for the field I am attempting to sum. I shouldn't need to enter a value as I am trying to obtain the value.
Is there an easy way to auto-populate a Junction table [in access 2010] given the following two tables with a many-to-many relationship for Tasks? The two tables are
Table 1) tblTasks (TaskID (PK), Description), and
Table 2) tblMeasures (MeasureID (PK), Description, Tasks)
If JCTN table is JCTN_Tasks_Measures (TaskID, MeasureID), is there a way to populate when tblTasks(TaskID) == tblMeasures (Tasks)?
I understand right off the bat if you're reaction is "don't duplicate data!!" -- mine would be too (don't fret, I know my normalization).
I've linked a table in my db to my Global Address Book in Outlook 2007 and, upon entering an employee number as a new record, would like to verify that the number entered is listed in the GAL and then pull in the associated name and location info.
The key is that I don't want this info to rely on the GAL going forward. For example, if an employee leaves or is no longer listed in the GAL, I don't want to lose the employee info (past data is needed for audit purposes). Note: I will be creating a report later to show if there are discrepancies between the GAL and my table, but that's another story...
So, what would be the best auto-fill options in Access 2007?
I run a charity pre-school and have invoices to send out. Some of the parents cant afford to pay the whole amount in one go, so we give them a payment plan. e.g. If an invoice is for a 6 week term we let them pay weekly. So I have a check box on my form to say "are you on a payment plan".
Then - I have an free form input box..."How many payments..." and the answer may for instance be '6'.
What I want to do now is dynamically create/display 6 date fields, to record what the agreed payment dates are.
but maybe the answer is 4, or 7, etc. So I need to create/display the correct number of payment date fields.
Basically in my order details table i have the following fields
Product
Unit
Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?
I have a table with an empty column called ActionID. I need to generate a numerical number that begins with 5000 and goes up by 1 number on each saved record. I have a basic form that links all of my other field to the table except the one I need to generate (ActionID) but will need that number saved to the table once its created.
View 11 Replies View RelatedI am writing a very complex 'if statement' query expression. I need to create an alias column based on data from two data fields.
Here's the logic:
True: If [PP] = "WG" or "WS" or "WL" and If [GR] > 10 Then "Skilled" or
[GR] Between 6-9 Then "Semi - Skilled" or [GR] <6 Then "Unskilled"
False: "GS"
How i would best combine values in a table to produce a 'primary key id number.'
For example: the first letter of a city in the ID and the next number available/auto number - Portsmouth -> P233
I know i can create this in a query however i want it as the unique ID for that record entry in a table. If that doesn't make sense i can try to elaborate some more.
I'm trying to create a query that can sum values of different fields in different tables...Can I sum values of a field and put the result into another field in different table?
View 3 Replies View RelatedI have at least 3 relates tables in my access database.the first(sessions) table stores session detailes like id,date,time
The second one (tblemployees) contains our employees details like name, idp ,the third (attreq) relates the first table to second it stores ids of sessions and ids of personals that which determine which personals have attended in special sessions.
Now,my problem is that I want when a personnel log in and opens "confirmed session"form ,the access check and open records that this person have attended. How can I do it?
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)
DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15
IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static
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.
thank you
-Tim
Below is my attachment file.
ٱ - checkbox
spec 1 is not a record. when u are adding a new checklist, the format should be like that. But i duNNo how to create table based on this.
tablename
-create
-view
-update
-no update
-remarks
but for spec 1 do i need to create another table?
As I usually concentrate on building the tables and fields in my initial stage, and do some experimenting to analyze the relationship.
However, when I'm done, I find it quite a hassle to go manually through the tables to add description to each field for documenting.
What I would like to do is create a query that will list all fields and its description. Now, I have found codes to retrieve either table or fields properties, including the description property, but am kind of stumped on how I can make it a SQL statement so I can create a temporary query, which will be pretty be one time thing, that can read all fields' description and allow me to type in it, save it.
If that's not possible, I suppose I could write code, using Allen Browne's example, that would loop through the fields' description, debugging to the immediate window and prompting me if I would like to add something to the blank description. However, this isn't exactly greatest as I need to see all fields so I can be sure I'm giving good descriptions.
I figured someone may have had this same problem and maybe came up with a solution?
I have a userform that has 4 textboxes and a command button.
The user types in box1 a Rack, in box2 a shelf, in box3 a place and in box4 a shelf.
The user types in Rack textbox 18
The user types in Bay textbox 4
The user types in Shelf textbox 2
The user types in Place textbox 3
I then would like to create that amount of records in a new Temp table.
The attached file shows what the results of the output should be.
The interface being used is a main form with various tabs and a subform on each of these tabs.
There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?
Is there a way of merging 2 fields together to create an additional field
my database consists of 4 main tables (in order of relationships)
*HeadOfficeDetails
*SiteDetails
*ContainersOnSite *Contracts2015-2016
For example;
Account Reference: TEST
Site Number: 001
and the field i would like to have;
Site Reference: TEST/001
I would also like that when i add a new site to that account i will have TEST/002....
I'm trying to create a form using a combo box to populate multiple fields and tables.
I've created a text field to display the added information using this format:
=Comboboxname.Column(x)
in the text box control source field, and this works for display purposes.However, I need it to populate this data into a field on a table.
For example:
My combo box looks up data that has 2 columns, Part Number and Description.
The control source for the combo box is "Part Number". And that populates the part number in the "Main" table no problem.
The text box I created using the above format in the control source populates the field in the form, but not the "Main" table.
Is there a way for the other (description) field to also populate the "Main" table as well?
Greetings all!!!
I have a database containing a table of employees and their rank. I want to record monthly details regarding these people: hours, performance, etc. Their rank can change dynamically from month to month. They may be a Supervisor for a month, and I want to change this value on a monthly basis. But I want to generate a Table which shows all employees belonging to each of the eight departments, but which has empty fields enabling me to record these monthly values. I then want to be able to pull up that data at any time to see these values for any given month in the past. I also want to add the values of each field and total them for the various departments providing monthly and yearly values on a departmental and organizational (grand total) level.
The employee names and their respective departments rarely change. I don't want to have to select their names (about 125 of them) every month to create a new blank form in which to put the monthly data. I want to have perhaps a button which creates the records and puts the names in for (1) department, (2) year, and (3) month, but that adds the fixed fields ready to put the data in. This data is required every month and the field names will never change. I'm sure the solution is programmatically simple, but I seem to have a mental block over the issue.
I hope that someone casts some light on this.
Do I use a Create Table query? (The data can remain in one table.) I would like to do it programmatically. My VB is pretty good, but, it seems, not good enough.
:confused:
Where a many to many relationships is resolved with a linking table...Is it possible to create an entry in the linking table where the two linked tables have the same value e.g. create a entry in the linking table where client table has same value as a job profile table i.e. both are in insurance. Therefore want to create an entry in the middle table with foreign keys from either link automatically
ta
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
Hi there :)
I'm not sure if I've picked the right forum topic for this... It's not a straightforward table question, and I think the only way I can do what I need is with a query of some sort. Apologies if it should have gone in 'Tables'...
The situation is: I'm creating a database (using Access 2003) in which our secretaries can enter exam marks for students. The difficulty is that there are ten papers (exams), each student's exam is marked by at least two examiners and there are at least 6 examiners per paper. Every examiner also marks more than one paper, but doesn't mark all papers.
So, I've got a table with all of the examiners and the papers they're marking and I've got a table with all the students and the papers they're taking. Because we have to enter the marks in such a way that we know both which mark an examiner gave and (obviously) which mark a student received (e.g. so we can look at statistics for each examiner), I thought the best way to do this would be to run a query that created a new table per paper in which the field names would be: Student_ID, <name of examiner 1 for the paper>, <name of examiner 2 for the paper>, <name of examiner 3 for the paper>, etc until the end of the list of examiners for that paper. The student's number would populate the Student_ID field and then the secretaries would enter each examiner's mark in their named field. Like this:
| Student_ID | A.N. Other | J. Blogs | J.Q. Public | Agreed_Mark |
1234A 72 74 73
2345B 65 68 67
3456C 71 73 72
4567D 52 51 52
Listing the name of every examiner (including those who aren't marking that particular paper) isn't an option (and would have to be hard coded into a table in any case, as far as I'm aware, either in a query or in a table design. This is something I'm loathe to do). Ideally, the secretary would be able to press a button on a form and have all the mark entry tables for all the papers created automagically.
The examiners for the different papers change every year, and not every academic marks exams every year, so this data will not remain static. Hard coding a table with examiner names would make more work for the secretaries. (I want to avoid this as all mark entering for all students is done in a single day. This means over 600 students at 3 marks per student [mark 1, mark 2, and final agreed mark] with time to contact examiners with questions... and only 2 secretaries, each of whom deals with a different section of students).
Because of the nature of the marking, it would also make quite a lot of work for the secretaries if we had to cross reference a generic Examiner_1 field with a different examiner for each paper, so the field name really must be the examiner's actual name.
Can anyone help me? I'm happy to do it with either VB or SQL; I'm not a programmer, but I can usually work out what code does even if I can't write it myself. I should be able to adapt anything presented as long as it's complete and doesn't assume that I know the basics. :) I've also got two reference books to hand: _Microsoft Access 2003 Forms, Reports and Queries_ by Paul McFedries and _How to Do Everything with Microsoft Office Access 2003_ by Virginia Anderson.
Thanks very much for your time. :)
-Jen