Information Entered On Two Different Tables At The Same Time
Oct 10, 2004
I am creating my first database and I'm a beginner. I have a table called customers and another table that has customer and product information. What I would like to do is when a new record for my product is entered it also enters the customer information on another table. I have repeat customers though, so I only want it to enter it once. Right now, I have to enter the customer info separately. Customer table contains: Customer ID, first, last, Phone. My product table also has this same info. Is this possible? To enter info on a product form and the selected info be copied into another table at the same time only once.
If you are using a form as a sort of user interface and you've got a user entering in information into the form, is there any way possible to print a preview of the currently entered form information without saving it to the database first?
I'm thinking a kind of validation thing. Like, "Click here and preview what you've entered. Sure this is what you want?" type of thing.
Here is my problem. We stage outages once 3 tickets have been recieved for the same problem type, but only if all three tickets are entered within 16.65 hours or 999 minutes. I.E. the third ticket is received within 999 minutes of the first ticket. How can I capture this based on the field time entered?
I have a form where I am capturing StartTime and EndTime of certain events. I have set the input mask as 99:00 >LL;0;_
My users think it is too much to enter am or pm. What they want is the system to calculate the am pm based on the time entered.
If the time entered is between 8:00 to 11:59, the system should save it as 11:59 am and if the time entered is between 12:00 to 7.00, the system should save it as 7.00 pm.
I have a question about summing information on a report. I am developing a report to see have percentage of time Staff work within a certain area. On the data base the time percentages entered as ranges e.g. 75-100%, or 11-25%. We needed to figure out the total percentage of time so I created a report to add the total time. I used a text box which it titled FTE_Total if embedded iif functions to display the total time. Here is the function I used:
This worked perfectly however I cannot get the FTE_Total fields to sum. I tried the following function in the group footer: =Sum([FTE_Total]) but everytime I try to look at the report in report view I get a message asking for the FTE_Total Parameter.
HELP!!! I am getting quite desperate. I am a novice. I have made a database for customers, products, etc. They all have good relations. I have made a query where I can see who bought what and when and how much it costs. Now here's the problem. When my prices in my product list (this is the table of which the prices show up in my form) have to be altered (because of prices going up), it automaticaly changes the query and the form!! According to messages stated in this forum, I have opened the properties in the text boxes and changed the price fields, "enabled" to no, "locked" to yes, and the forms DataEntry to yes. Strangely in the North Wind example, the prices shown in the form (entry form), are extracted from the subformdetails table. But how can you extract something from a table if there is nothing in the table?? Totaly I have four tables (Orders, Orderdetails, Products and Customers) Alle these tables are joined on a one to many basis. Please can somebody help me..... Thank you Anouk (Netherlands) :confused:
I have a simple table with just 5 field. When trying to sort i get error message "the value you entered is not valed for the sort id". I get this for three of the fields.
Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
I have a calculated field that is a total based on 5 other fields. These fields are rankings of priority on individual categories, while the calculated field is a total priority ranking for the entire record. (A ticket) The 5 categories are prioritized, and the calculated field runs it through our equation to determine the overall priority of each ticket.
Now, some tickets don't have individual priority numbers in their categories. Therefore, no total priority number is calculated in the calculated field. We would like these tickets to be given a manual total priority, but we can't manually enter numbers into the calculated field to do this.
Is there a way of saying this to the calculated field... "Use the equation to run the individual categories to come up with your total. IF there are no numbers in those categories, reference the field 'Manual Priority' to find your number."
This way, we'd like to keep all of the total priorities in the calculated field. It's just that some of them have to be manually entered because there will be no individual priority categories to calculate the total by.
The current formula for this total priority field is...
(([Size of Financial Risk?]*20)+([Impact on Internal Customers]*15)+([Non-Financial Impact]*20)+([Impact on External Customers]*30)+([Estimated # of Clients Impacted]*15))
I'd like to say, "Do that... or use this manual field if that equation turns out to be nothing."
I would like my table to show the result of a calculations (or a formula). I surmise that you can not have a column cell calculate data, however if I can do this in the Form View and on a Report - can it just SHOW on the Table so I do not have to keep switching back and forth to see the information.
I am new to Access and think the forum will be a big help to me. Thanks
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 want a default value to equal the first 2 characters of field [first name] and first 3 characters of field [last name] and the numeric datevalue of [DOB] 'date of birth'.
In excel its easy, C D E resultformula AndrewTester12/12/1980AnTes-29567
Formula LEFT(c9,2)&LEFT(d9,3)&"-"&e9
How do I get same result in Access?
tried
Field 'PRN', a text field set as default =""""& left([first name],2}+LEFT(Last name],3), date()&""""
Access accepted the above statement but when I input user details the default doesn't work.
I am trying to color code text boxes based on the data entered in. The column is "PHASE" and the items entered in are either "1" "2" or "3". 1 is supposed to be Red, 2 Yellow, 3 Green.
I have Microsoft Access XP (I'm sure). I've tried to do it on VB and had no luck. I couldn't figure out Macro and I don't have On Click or Code Builder.
I have tried a couple of different expressions and no luck. I'm definitely an amateur to Access. I'm only using it cause it's what we have in the office.
I'm having trouble converting text to proper text in Access 2010 using Windows 8.1 as OS.
The message I keep getting is:
The expression you entered contains invalid syntax. You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.
I am new with Access and fear that this might be a very elementary question - apologies. Basically, I would like to create a few different tables that all have the same base information (like the primary key and name of the data), but different additional information. The only thing that I can't seem to figure out is how to be able to add a new entry to one table and have it show it up in the other tables. Is this possible? Thank you!
Here is my problem, as you will see. In the from called frmStaff, I have created 2 lookup tables that work fine in the main form. In the subform under activites though, I have attempted to create a lookup form for Name and Location of activities. It does not appear to be storing the information from previous entries though. My aim is so that when someone goes to enter a new activity name or location, the data will be saved and be in the list for the lookup next time with a different entry, however, it does not appear to be storing the information in the appropriate table. Any ideas on how to fix this?
I am getting information on products stored in a query. I want that query to check a couple tables to see if an item is linked through all of them. If it is not listed in the last table (catalog) I want it to be shown.
Code:Catalog tableVolume Prefixprodno Price15 - CE 0218 9.9915 - CE 0722 3.7215 - CF 0218 12.3615 - CF 0091 14.00Source TableSource MediaCM70904 15 - CE
Code:Batches QueryCustomer Number Prefixprodno Source716933 0218 CM70904716933 0408 CM70904
This is the Select Statement I'm using, which gets the 0218 but not the 0408 I want
Code:SELECT (fieldnames......)FROM [Batches] INNER JOIN ([Source] INNER JOIN [Catalog] ON [Source].[Media] = [Catalog].Volume) ON ([Batches].Source = [Source].[Source]) AND ([Batches].Prefixprodno IN ([Catalog].Prefixprodno))WHERE [Batches].[Customer Number]=716933;
If I do NOT IN I get both 0218 and 0408. I just want 0408. I'm sorry if this seems confusing but any help would be amazing at this point =/
In the scheme below, I want to fetch all the info in Table3 for all the Tiers that has the AppID in Table2. There could be more than 1 Tier using the AppID. I don't know if I'm clear though.
Code:
Table1 Table2 Table3
AppID (PK) _ AutoNb (PK) AutoNB Name Tier (FK) ---_ Name - AppID (FK) \_ Tier (PK)
Here's my failing attempt...
Code:
SELECT Table3.* FROM Table3 WHERE Table3.Tier = (SELECT AppID FROM Table1 WHERE Table1.AppID = 2002);
I am brand new to building a database.What I want is a database to store Quote, Job and Invoicing information. We receive quotes first and then they can, but don't always, turn into jobs. We can also receive a job without quoting it. We currently have two spreadsheets. One is for Quotes and the other is for Jobs.
QUOTES INFORMATION Quote # Date Customer Name Part # Part Name Quote Due Date Qty Lead Time Price Unit
JOBS INFORMATION Job # Qty Quote # Customer Name PO# Part # Part Name Est Hours Start Date Due Date Price
As you can see a lot of the information in the Quote spreadsheet is also used in the Job spreadsheet. (Bold represents duplicated items) We currently type the information into each spreadsheet.
Then there are different forms that are filled out for quotes and jobs that contain the information in the spreadsheets.Is there a way that I can have the QUOTE Table automatically populate the JOB Table information?
I'm trying to import information from an xls file into an Access database.. I understand how to use the File --> Get External Data --> Import option in Access, however I only have the option of importing the information into Sheet1, Sheet2 etc
I already have a table named Info1 which I want to import the information into
I have an historical database table that contains information relating to soldiers. Additionally I have many photographs, and other documents appertaining to these individual soldiers stored in folders on my PC hard drive and I would like ideally, to be able to have a link from my database to these other items, so that when I open a a soldiers record I am able to click on a link that will take me to a folder containing the photographs, letters and further documents relating to him.
However this is where I am at a loss as to how to proceed, and how I could have this facility, I run Access 2007 by the way.
I essentially created an account since I can't seem to find a straightforward answer much anywhere else (plenty of hints on auto-filling forms- but that isn't particularly conducive to my specific need on this).
I've attached a blank copy of my database.
The immediate concern I'm having is that I want to be able to autofill data in the invoice table based on the customer table and the products/service table.
*I want to be able to select a first name [or other primary key if necessary] to fill in the last name, company, address, city/state/zip, phone, fax, cell/alt phone, and email automatically... that is without multiple dropdown selections or input to those sections at all.
*I want prices to fill in to the 'cost of product/service X' so that I may use it for other calculations in the invoice table- as well as to make forms from it directly.
I have created two tables. One table list of 100 Categories that I monitor each month. The Categories are never changing month-to month, however, each might be associated with a different client month-to-month. Presently, I am manually typing in the Client information month-to-month with information pulled from the Client's table. The Client's table has a Category Field which is populated when a Client has purchased space to use it.
In essence, I have the Category Table (Fields: Record Number; CategoryName; Client Name; beginning date the client will use the Category and Ending Date the client will stop using it). The Client's Table has a lot more fields/information but it still has the same fields as the Category Table. I am trying to be able to use the Category Table and have it automatically populate with the client who is using the Category at that time. Any Category not being used by a Client then the Query should write "Open" in the Client's name field. I have tried many different scenarios but can't seem to get it to work. I must see all 100 Categories each time I run a report. It doesn't have to say "Open" but where ever a client did not use the Category it should be left blank.
