I have 2 tables (tblSET, tblPROJECT) that have a relationship between them in the form of strPROJID (Project ID)
I need to make a form that has all empty fields in it, and when filled in distributes the entered data to the corresponding field in the corresponding table. I have tried using the design wizard and just putting the fields from all tables onto one form, but the "new record" button is greyed out when I do that. I figure this will include coding of some sort but I am far from a coder.
I have seperated my data into seperate tables and set up one to many relationships.
tblincident -------------- Key# Report# Time Date Location
tblPerson ---------------- Key# Last Name First Name Middle Name Address etc.
tblItem ---------------- Key# Make Model Serial# etc.
What i want to do is use one form to enter all this data into the different tables.
There can only be one record in tblIncident that can match multiple records in the other tables.
i.e. for each record in tblIncident, I want to be able to have the capability to have as many persons related to that incident as I need.
I was thinking about setting up a tabctl for the data that has to be entered for tblIncident and then change the property setting for it to go away and then have the next tabctl appear in it's place for entering data into the next table. The problem I am having is that apparently I can only have one table as the control source for the form.
So next I tried using subforms, but that isn't working either. I can't figure out how to make the subform appear in a specific place on my form, sized in specific dimensions I want where I want.
I am just starting to learn about this relationships stuff and it's kind of difficult. How do you make it where you can do this from one form? Do I need to make a query of some sort?
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 am new to Access here, and looking to start up a database that will contain data for thousands of footballers.
I have on one table ("Directory"), all 5000+ players, with nationalities, names etc, and on another ("Flags"), I have all nations of the world and their respective flags (as an attachment).
How do I add the flag of a players nation into the "Directory" table for each record? Is there a quick way? I've tried "Lookup" but it won't let me do it for images, I've tried "Relationships" but without success.....surely I don't have to go through the "add attachment" process for all 5000 records?
I want to list all the records of a particular field from all tables in my database. The field has the same name in all the tables. Ideally I would like one long list of the records. How can I do this?
I have a pretty normalized Access Database. The table that I am trying to add new records is tbl_returns and has 4 fields: return_ID, serial, reason and inv_num.
When I sell a card (or a range of the cards) an invoice including all the information is saved as a record in a new row in tbl_invoices.
If a vendor wanted to return a card (or a range of the cards) in the next visit (weeks or months later) I will accept and in most cases they want me to switch the cards with new ones. Therefore in a new invoice (different date and invoice number) I will give him new cards and return the cards that he wanted to change or return.
Now I have to assign NULL to the inv_ID field in tbl_allPins in order to make it available for sell in future. At the same time I want to have a record of the returned cards including serial number , the reasoning of return, invoice number and/or a little note about each one/range of the card(s).These are to be recorded in tbl_returns as you can see.
For instance you want to return serial numbers between 9876 and 9880 (includes 5 cards) because of the "scratch off problems" and your invoice number is 22222, using frm_returns. After you process it and then open the tbl_returns to check the result, you will see 6 records are added instead of 5. I was able to understand why it is happening (I believe so!) but I could not fix it. Also I cold not write a VBA to remove the inv_ID in front of the related serial numbers in tbl_allPins.
Also in another trial was ended up to creating the Form1. Form1 looks better (has no extra records) but I have trouble to navigate through the records in tbl_returns. There was a sub-form added but it was showing all the records in tbl_returns which is unwanted.
By the way, serial numbers and PIN numbers are each a unique number in tbl_allPins.
I am connecting to a table in our accounting software using MS Access ODBC Connection. I would like to change the value in a table for every record in the table 7,000+. In the latest version of the software a new field was added to the Vendor Table that by default is unchecked. I would like to have this field checked for each vendor. Is there a way that I can change all the records at once instead of going row by row to make the changes?
I have a main form that has fields from different tables. This is a research study, so each form enters data into different tables. Well, each record is a person's data. Instead of scrolling through each record, I need an option on the main form that allows me to search for a specific person and have their data populate into the form.So far I am able to make a combo box that pulls up the record from one table. Well, HOW do i do it from ALL the tables!?? Do I have to make a query?
I have two tables with compatible data. Table 1 has 23 records and table 2 has 5 records. I am trying to make a "make Table". But instead of a table with a combined 28 results, I get 115.
Now the "User" table has the ff. fields: ID(auto num),FName,MName,LName,User,Pass,Type.
Now what I want to do is, if I add a new a record in the table "User" and if the record has the data "Admin" in the field "Type" then the record should also be saved in the table "Admin" but if the data is "Teacher" or "Pupil" then it should be also saved in their respective tables.
I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.
The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.
I have been trying to create a report to count equipment tested between two dates:
I am using Access 2000 (old I know) and I have 21 different tables with the fields laid out the same. For the moment, I will list five of the tables: Servers, Laptops, Printers, Workstations, and Monitors. The criteria I draw from each table are the fields Model#, Part#, Serial#, Test Date, Retest Date, and Technician.
I can create a report from a query (say laptops). The criteria I is BETWEEN[Enter Start Date]AND[Enter End Date] under the TEST DATE field. Works great! In my report I use =Count(*)&" "&"Unit(s) tested" & "Between "&[Enter Start Date] & " and" & [Enter End Date].
That works great too.
I am trying to create ONE REPORT using ONE Date range and display how many units were tested:
Example:
Units Tested between March 1, 2014 and March 31, 2014
If I can get these five tables, hopefully I can add additional tables to the same report as I need them. I am not savy with VB code, but will give it a go with some direction as to where to put it and how to add to it.
I have been unable to find a simple way to copy a text entry from a Table Field and paste it to multiple records in Datasheet view.The Toolbar 'Paste' procedure copies to the selected record only and I am unaware of how to select multiple records, as per a spreadsheet procedure.
I have a form (form1) that will populate with records (table1) based on key fields of "Project Number" and at time-points we will update part of the record, and then we create a word document detailing the updates with a couple of other bits of information not in original record.
Basically I want it to go from the original form (form1) into another form (form2) (via a command button) taking the key field of "Project Number" where it will ask the bits of information that we don't need have already and the contact person.
I have got this to store in a different table (table2) using a key field of just "ID" from the original records (table1). A list of contact people are in a separate table (table3) using a key field of "contact name" which will include other details for them such as address and phone number.
So from "form2" I would like to produce a report that contains information form "table1" based on the "Project Number" and "table2" based on "ID" as just been generated and "table 3" which is based on "contact name".
So I want to pull information form the records based on the certain key fields but struggling for it to pull it successfully...
Got relationships between: "contact name" fields in both "table2" and "table3" "project number" fields in both "table1" and table2"
In a query for the report I have all the appropriate fields, but only have the "contact name" and "project number" from "table2" is this right? or do I need both in?
Or would it be easier to draw all this information into "form2" but don't really want to select that many drop down boxes or would it be able to pull it from the "contact name" and "project number" boxes????
I am trying to create a database by compiling records from multiple excel sheets. however, since most of the clients still uses the excel sheets to enter records, I am thinking if it's possible to create a linked table in access from these excel sheets that are hosted on an online server.
also is it possible to merge multiple linked tables together? if they have the same field range, so they will append when the linked tables gets updated.
Hello, Limited access user, here is what i am trying to do.
I keep records of members of a racing organization using access. Some users have have multiple vehicles. Each driver and each vehicle have a number. On the form i use to input the data, i am trying to have the member and their vehicles, however it will only allow one vehicle ID number. How could I have a group of fields on a form, where the data of those fields could be associated with each other.
So that when i get an application, I can put all the info on one form, member name, and member ID, vehicle name vehicle ID, one two or three times, with different names and numbers, but one field so i can still use vehicle ID to do sorts and such.
:rolleyes: Hmm how should I put it? I want like to create a form so I can show 8 to 10 records at a time on a single page (instead of using continuous form); so when I press the next button, the next set of 8 records will be show on the next page.
I want to create a form in MS access for General Journal input for an accounting software. Problem is that my client required a form in which he can input as many Debits and/or credits as he likes, for one transaction on one form. For example it may be 2 debits and 4 Credits in one transaction. Its a simple one table entry with fields; TrNo (Transaction Number), Date, Type (Debit or Credit), Amount, and Narration (for details of transaction). Out of these field TrNo, Date & Narration will be same for all debits & credits in one transaction.
Basically I have a table where freelancers have multiple records for each module they teach. What I am trying to do is show all records for each person onto the same form, but anything I have followed still only brings up the first record.
This information is all on the same table, so would this be easier to be on separate tables to then create relationships?
I am currently building a database with the purpose of capturing data about various employees' skills. The idea is that a supervisor can fill out a questionnaire of sorts, with this employee, and give them a rating out of 5 for each skill. I have been working on this for a few days, and I am most certainly not an Access genius, so I have come to a bit of a wall. I know where I want to get, but I don't know how or if it is even possible, to get there.
I want to have a form that has all the questions on it, so each time the supervisor pulls it up they can just tab through and fill out the form. I have a main form with a subform, although this isn't ideal, it is the only way I can think of to even get close to what I am looking for. The main form portion, after a bit of testing, seems to be producing the desired results I am looking for in populating the specified table, but my subform (questionnaire) is just not doing what I had hoped. I know some of my formatting will change, but I just need a way to allow the supervisors to fill out the questionnaire, hit a button that adds all of the information in both the mainform and subform to their respective table(s) (which I know will require multiple records-1 for each question), and then be able to produce reports by employee.
I want various text boxes to autofill with the questionnaire info, and then have a box for the supervisor to input the ratings. I have used a Dlookup for the default property in each of the question and description text boxes in the subform, so they will reference info from a questionnaire table.
I have 6 tables that have 1 to many relationships. I would like to have all 6 tables on 1 form for data entry. When I use the “Form wizard” and select the tables it wants to group the last 3 tables in 1 subform and it won’t let me add records to the last 2 tables. Same thing happens when I create a query and run the form wizard selecting the query.
I know I’m doing something wrong but I can’t see it……….
Thanks Paul
Here's an add on question:
When the form wizard adds a subform to a subform the link child and master are filled out and the form updates. But when I add another subform and set the links it will not update. Example: form Book has text fiels and 2 sub forms "Chapter" and "Section" subform section child link is ChapterID, master link is [Chapter Subform].Form![ChapterID] --- This updates as expected.
I create a new subform "para" based on a table and set the Child link as SectID, master link [SectionID].Form![Section Subform].Form![SectionID] --- Will not update the subform "para" when the next record in subform "section" is selected.
I think I have created the relationship fairly well. Most of them one-to-many.
I created the form but now I whenever person is making entry I want the information from one form to go into multiples tables. What's the best to go about this?
I have tried couple different ways, Made a query from the tables and then using that as Record Source for the form as well. It doesn't seem to work properly.
