I am having an issue with a database I'm creating. It is a root cause analysis database. One of the fields is Target_Date. I need to set up queries that alert us if the target date is coming up, (in a week or so), or if it is past due. The issue is that some target dates are simply, "Next Run", which is to say that the corrective action must happen on the next run of the particular item which, at that point, has not been scheduled.
In order to do the above query, the dates must be formatted as Date/Time as <date() will bring up all dates regardless of whether they are in fact less than today if they are in text format. However, this restricts the ability to put in the target_date of "Next Run". Next Run will be required for another query in which we will specify the products coming up and it will let us know which "Next Run" items associated with those products we will do.
Essentially, I have taken the long route to ask, is it possible to create a field format that measures dates as dates and text as text?
I have been trying to figure this one out for a while and I can't seem to find anything helpful. I'm going to present a simplified version of my problem to help keep it simple.
I have an "Activities" table that contains fields: ActivityID ActivityName
I have a table of 7 Risk areas which contains fields: RiskID RiskName
What I need to do is to create a form that will allow the user to select an activity and then, for each of the 7 risk areas, input a risk measurement.
This involves the creation of a 3rd table to collect the user input which would contain fields:
MeasurementID RiskMeasurement ActivityID RiskID
I can do a dropdown of the Activities and have a subform that links to it but the problem I'm having is trying to get that subform to list the 7 Risk Areas AND collect the RiskMeasurement information from the user. How can I have a listing of these 7 areas from one table that are tied to inputs that go into another table? This seems so basic and simple yet I have tried numerous things and all have failed.
I need to build a output text file (edi) with 500 byte records and up to 6 different record formats. Can anyone point me to any information/links that would help me understand how to do this?
I have a shared db where there is a requirement for more than 1 user to enter data in the same form and hence the same table. Up till now there has only been 1 data entry clerk so there has not been a problem.
Would accessing the same form by different users cause a problem, assumming they are inputting different records?
I am currently working on a form that involves several linked tables. Since our personnel got so used to a certain form format, I have scanned the form and imported into an access form as a background and have laid corresponding fields on top.(I have attached a snapshot of the form). This works great for a view only form, but I can't seem to add/edit records using this form because of the table relationship. I have done some research, and found that one way to get around this problem is to use subforms, but that would kill the form format. Another way I have seen (which I am not sure how to do) is to have a temporary field and once you hit the submit button it will then fill out the tables.
I'm pretty new to making databases outside of a basic access class..Is it possible to make a record in one table that makes a new record in 5 different tables using different bits of the initial record?I want to use the data entered in an evaluation form to create a new entry with the basic identifying information in 4 different tables.
I have a very simple database that keeps a track of line performance figures, i have attached a part of it as an example:
In my lines table are 8 production lines
in my log table i need to keep a track of the data,
what i would like to happen:
i need a form that when i open it all the production lines i have are displayed and next to each line are 2 data entry fields so i can enter a performance and an output figure for each line in one go, so in this instance i would like 8 lines and 2 entries per line.
at the moment i have to do each line individually.
i would also like this to grow should i add more lines
I am new to Access in a way, and developed a small tool as a warehouse management system. the system is working great but I need to add a rule or condition to this tool.
I have Tables having warehouses locations (LOCATION1,LOCATION2, LOCATION3, etc...)
Some items (products) will be entered into a transaction table where with queries and reports, can see each item in which location it is or in a particular location what are the products stored in.
however, if there is no limitation or condition in a way or another into the access application to limit users from entering an item OUT in the transaction from a location which doesn't have this product, I need access to tell me a message or stop me from entering that transaction.
To explain more, if I have ProductA in LOCATION1, and one of the data entry guys entered a transaction in LOCATION2 for the same ProductA, access will not allow him since there is no quantity of that particular product in that LOCATION2.
How to do it? I checked in Data Validation and Validation rules, but its either hard for me to understand or am not getting how to do it?
Hello, I understand that this is against any database rules but I need to add data to my database concurrently in two tables.
I will use one form (frmMaster) and write my data to my main table (table1). At the same time, I need some of this data to be stored into another table (table2).
I do not want to use an append query.
Is this possible via code withough having to close the active form? Can anyone help with with some code?
Right now i have a qry that takes ID - Name - Order - RepeatOrder - Month - Department.
Now when i run the qry what i want is a grand total of Order/Repeat Order by Name for the Month, what is happening is the qry is creating multiple entries for different departments. I even have Department "Group by" but still causing the issue, For example:
For APR:
4/1/14 Dep A 12 2 4/1/14 Dep B 10 2
However i do not want them separated, how to i merge the data into 1 entry? 4/1/14 22 4?
I have been battling to get a mail merge to work correctly with a access database, the data isn't coming across correctly.
I have the date set up in access as a long date format, when it merges into word it changes to 2005-10-05 00:00:00, can anyone tell me what I need to do to get the date to appear correct.
thanks in advance
Di
ps I have read and re read the help pages on this but still can't get it to work
I spent a pretty significant amount of time working with Access in college, but that was years ago. Now that I'm actually trying to do something practical with it in the office, I can't remember how to do anything! This board has been helpful to read so far, and hopefully you can help me with my specific question.In our system, shipping charges by unit vary between customers. I have a table set up with a field associating a customer's charge with the customer's name. The problem is, the two most common charge amounts are $0.25/unit and $0.305/unit (dealing in fractions of a cent here). Right now the field is set to show 3 decimal places, so the numbers are showing up as $0.250 and $0.305 respectively. While this works and all, I want to format the field so that when a currency figure with 2 decimal places is input, it will display only 2 decimal places, but in the event you enter one with three decimal places, all 3 places are shown (and the number is not rounded off).Can anyone help me? My database is in its beginning stages, and I'm still somewhat of a beginner, so the less complicated, the better.Thanks!
Can I use subforms to create ONE form that will do data entry to more than 3 tables at once in a relational db? How can I do this? Do I have to use Javascript, etc?
I am designing a little database to hold physical activity log info for 2 school districts, required by a grant. Four-five times each year every kid (2000) will complete a log form for 3 days that lists separately for 34 intervals (1/2 hour each, from 7am - midnight) in which physical activity s/he engaged (59 options) and the level of effort of each (4 options, from light to very hard).
The log sheets look like a datasheet with 5 blank columns: #1 enter the activity code (1-59) and #2-5 mark an "X" in the column describing level of effort (light to very hard). There are 34 rows, each labeled from 7am thru midnight in intervals of 30 minutes. The kids **have to** enter an activity code and a level of effort on every row. They do this for three days in a row, 4-5 times during the year. (believe it or not ... federal grant requirement)
I am maintaining survey responses (also 4-5 times a year) in this same database. I have a basic demographic table (Id, name, grade level, school). I am also maintaining eating habits (4-5 times per year) from food logs, like the activity logs, but these are for 5-7 days at a time. Finally, I will be importing number of steps walked for 4-7 days (also 4-5 times each year) from pedometer downloads, one per kid.
I have all tables and data entry forms except for this activity log set up, all data entered for the baseline period. What I would like to do with the activity log is something like a main form showing kid name, ID. I think I want to enter each row of the log (34 rows) into something like a datasheet, each row with a date field (I will use CTRL-' to copy down for one page of the paper log), and an activity code field (1-59 as codes) and a level of effort field (1-4, light to very hard).
What I think I want is one table for activity info, each record with these fields:
Kid_ID Date_Logged Time_Interval (these are the 34 rows, 7am - midnight, in 1/2 hour intervals) Activity_Code (options 1-59) Level_of_Effort (options 1-4)
I can see this in a datasheet format, but entering 2000 kids * 3 pages * 4-5 times a year [* 3 years] into Excel does not seem to be an efficient solution.
If I could open a form on the screen with a combo box to pick a kid and then pick a date, then fill in 34 rows of activity codes (1-59 as options) with a level of effort for each (1-4 as options), that would be great.
My obstacle right now is how to display 34 empty rows already loaded with an index related to the time interval, so that I don't have to enter that for each row -
Hence my subject descriptor, "data entry in matrix format". I am using Access 2007, Windows XP Pro (all service packs etc. up to date).
I am creating a table for data entry. Three fields in the table are going to be Firstname, Lastname, and Address. I want the choices for data entry to be read from a master table which contains first and last names and town of residence.
That being said I would like the choice of Lastname to be all last names from the master table, and the choices of Firstname to be those from the master table but are limited to having the Lastname as entered in the previous field, finally I want the Address field to be limited to those records which match the lastname and firstname. I have been playing around with lookup queries for each of the fields to no avail.
How do I create a form that will allow me to enter data into three different tables?
I have a rather simple database for tracking students. When I get a new student I need to take information off their paper application and enter it into three separate tables, Student, Families, and Demographics. Each of these tables contains the fields StudentID, StudentFirst, and StudentLast.There is a relationship between the StudentID field on each table, with Student being the main table and Families and Demographics coming off of it. All relationships are one to many. StudentID is the key for the table Student.
I want to create a form to add a new record to each of these tables. I want my data to be displayed in the Columnar style. I only want to enter StudentID, StudentFirst and Studentlast once and have it populate to all three tables.
I can create a form and a query that displays existing entries exactly like how I want to enter them, but of course I can not edit or add to them. I have tried using subforms, but they don't seem to link up. Once I enter the Name, I want it to be on all the forms. I don't want to have type it three times. I also tried creating three separate forms and connect them with the Navigation Form. The issue again is that once I enter the name in one form, it is not automatically on the next form.
I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.
For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...
I have tried using this: Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"
Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.
I have designed a form that holds data from a table called Purchase, it also has got 3 more fields that are coming from another table called Master. These 3 fields, Code No, PartNo and Description are same in both tables. All i need is a way to bring the data of the stock table on purchase form. As soon as i enter code number in code field, it should check in stock table if the same code exist, if yes then it should bring the all 3 fields in purchase form and display.
I have a form, created from a query. THe query contains an links about six tables using an AutoNumber ID.
OK, so when users enter their data into this form, I would like the autoincremented number to increase in each of these tables. The catch is that when the user enters data he/she does not have to enter data into every field.
So, some of my tables do not auto-increment and some do. How should I go about resolving this so that when someone enters new data, each table's ID is autoincremented no matter if that table contains information immediately or not? Please Help.:confused:
hi, i am new to access and struggling so any help would be great...
i have a db with 3 tables. students contains a Primary Key ('studentid') and then 'firstname' and 'lastname' fields. The two other tables (contactdetails, coursedetails) have 'studentid' as a Primary Key and then they each have other fields.
I set up relationships between the tables (1 to 1) but i would now like to create a form that will allow me to enter data to all three tables at the same time. (this would be ideal as it keeps the data normalised) if this isn't possible i would like to create a form that adds the 'studentid' record to each of the tables.
let me know if you need anymore details but if someone can point me in the right direction it would be cool.
I am a new user to access and are therefore seeking the assistance of a more experienced user (I'm sure this will be an easy one for all out there).
I want to be able to get records from different tables after the table name has been chosen.
In other words and to be a bit more specific, at the moment in one table I am selecting the tables I want to get information from. Once I select the tables I need to then be able to extract the required data from the selected tables and then put it into a new table with summary totals.
Maybe a noob question, but I'm still learning access (I know a little late at the age of 37, but better late then never). I have a form with several tabs, linked to a table with employee information, works like a charm. Now, my last tab is called settings, maybe no need to say that the information from this tab needs to be called from a different table called settings. Certain information, like version number, department, etc I want to display all the time in the header and I want to be able to manipulate that information through the settings tab. I definately don't want any relationships with the employee table. Is there a simple way to achieve this, I tried drag and drop but this leads to errors, so definately I do something wrong here. Much appreciated,
First of all, I'm kind of a newbie to all of this, but not entirely, bare this in mind please ;-) Also I'm from the Netherlands and my english might be a bit rusty, so bear with me if you please.... :-)
I'm having a problem for which I cannot seem to find a solution. Below I've outlined the situation:
I have a couple of tables up to now:
CustomerTable CaseDateTimeTable CaseTechInfoTable
CustomerTable has got a primary key: Clientnumber CaseDateTimeTable has primary key: Casenumber CaseTechInfoTable has no primary keys.
CustomerTable only includes (apart from all adress info etc) the clientnumber field which is of importance in this case i think. CaseDateTimeTable includes both Casenumber and clientnumber. CaseTechInfoTable also includes both Casenumber and clientnumber. Only both other tables have Primary keys, and this one doesn't.
I've created a relation between CustomerTable - Clientnumber and CaseDateTimeTable - Clientnumber. Another relation I've made is between CaseDateTimeTable - casenumber and CaseTechInfo - casenumber.
Now, I have made a form in which the name and adress data is fed into the database, in normal form view. Below these textboxes etc. I've created a SubForm in datasheet view in which the existing cases from the at that time selected/displayed customer should be displayed. At the moment only fields from the CaseDateTimeTable can be displayed, but I would also like to display some fields from, for example, CaseTechInfoTable in that same subform.
But how?
When I create a (sub)form via the wizard and for example select: CaseDateTimeTable - clientnumber CaseDateTimeTable - Casenumber CaseTechInfoTable - Problem description
and display these fields in the subform as a datasheet, then the separate cases from each customer are not displayed. I have 5 clients in my test dbase, just about all of them have 2 or 3 cases associated with them. In the example above only the data from two clients is displayed and then only from 1 case each, not the actual amount of cases. So for example:
clientnumber 4 has casenumber 5 and casenumber 6. But only casenumber 5 is displayed. The same happens for clientnumber 2, he has casenumbers 9 and 3 but only casenumber 9 is displayed.
The strange thing is though, that when I create the same (sub)form in datasheet view, but only use fields from the CaseDateTimeTable all cases per client are nicely displayed in the subform.
Why? And how do I solve this problem? I need to solve it so that I can add data from other tables into the subform later on so a nice overview of certain important data can be given per customer per case, even before the case form(s) is/are opened.
I've tried making a query and base a form on that, but for some reason I can't get the query to display all my customers, let alone all cases associated per customer. In the past I have made another (smaller and simpler) database for which I also made a lot of queries. But now? Pff... I'm lost at the moment.
