I have an autolookup that changes the numbers 2, 4 and 6 into P, M, and D so they look easier for my purpose. However, when I use a select query to find the max number for each criteria it shows a number not the letter. Do lookups stop working when you apply maths or can i change this.
View RepliesHi
I am trying to do a calculation using 2 fields in 2 different tables
I want the result to be displayed in a field so I have put this expression in the control source of that field:
=[test2]+Forms!table1!test
But all I get is #Name?
Any ideas what is wrong
I have what I think is a normalised database that uses foreign keys quite a bit.
For example if I had a 'contracts' table which refers to 'clients', then there would be a tblClients, where each client has a primary key. The tblContracts would then refer to the relevant client via that clients FK only which would be linked to the client's PK in tblClients via a one-to-many relationship.
If I need any user for any purpose to see contract related information that makes sense to a human being, I simply construct a query with the necessary relationships that will show client information alongside contract information by substituting tblContracts' client FK with required information from tblClients via the appropriate relationships.
I think that is reasonably basic stuff (hopefully correct practice!)
But what about when I come to import a block of new data that needs to go into tblContracts? I'm not going to be given a list of client keys (obviously) I'm going to given their real names.
MS Access has (in theory) all the information it needs (via the relationships) to substitute client IDs (keys) for their real names and thus slot these IDs into tblContracts with the new data as appropriate, but how do I make it do this? (I know it could kick out errors if there are any duplicate client names, but let's put that to one side for a moment).
I know that i can edit contents in a single table query and it will update the data in the underlying table, but how do i gain the ability to change data in a query when 3 tables are involved?
View 4 Replies View RelatedHello, I'm stuggling to work out how to go about setting up a formula.
I have 497 students 186 are Male 311 are Female. How do i work out the percentage of Male and Female students? I have no idea how to do the Math behind this so cannot start to add it to my database. If anyone can show me how to calculate this it would be really helpful.
regards
Pete
OK, I have this formula in an excel cell that works out the change in percentage of two cells, the formula is as follows.
IF(B3=0,O3,((+B3-O3)/B3))
The cells used are B3 and 03 and the results are stored in the AA3 cell.
I am trying to write this in a text field on a report which does the same thing but i'm having probelms translating this across. Can anyone point me in the right direction ?
Thanks in advance, Mitch.
Hi
sorry if this is a dumb request....
I have a table in which I store: 'cost price' , 'sell price' & 'Profit'
what method can I use to automatically populate the 'profit' based on the entry of 'cost' and 'sale' price???
Thanks in anticipation
I apologize for asking yet another question about autolookup but I have poured of Pat Hartman's example and all the posts in this forum and still can't seem to make it work. I have attached my db. I want to type in the asset number and autofill the rest of the form. Can someone please look at this and tell me what I am doing wrong. Thank you very much.
View 2 Replies View RelatedI am building a purchase order database that derives its subcontractor information from a separate table.
I want to be able to drop down a list for the subcontractors and once selected, the entire subcon area will be populated.
Another section of this PO is the project name, etc., which is a similar scenario, which has to be populated in the same manner.
Word file shows the structure of the PO.
Any help would be greatly appreciated!
jan.bodlund@shaw.ca
I'm trying to establish a ticket/citation database for a college security office at which i work at. I have two tables, table 1 includes the ticket information (date of ticket, citation number, *tag #, amount etc.), table 2 includes Repeted offenders information such as (name, address, city, zip, phone number, student ID# etc.).
I would like to have a table or query(preferably table) which automatically matches tag#'s to student information as soon as a new ticket is entered, *if the student information is available.
Thanks a lot in advance
I was just wondering if anyone knows how to sort of mask an ID as text. For example in this assignment I am doing at university I have a field named Flower ID which is a number. However, instead of displayign that number in forms and tables I want it to display its equivalent Species and Variety of flower. Is there anyway to fix this? Thanks for your help.
View 2 Replies View RelatedI'm getting very frustrated with trying to get this working. I've tried following other suggestions in this forum but must be mucking up on one vital peice.
From a choice in a combo box I want other values filled in on a form.
Can someone link me to a small, easy to follow example? I think I've got the query part sussed but fall down on the form side.Cheers:(
Hi there!
It's been almost 10 years since I've done any work with databases, so I'm really REALLY rusty and I barely remember how to do anything. I was wondering if you guys could help me out with a question I have in making a form.
I have 2 tables. The first table has a list of names and ID's. There exist some duplicate names, as some people are associated with more than one ID.
And there are two fields to the ID. One is Called the Client Code, and the other is the Client Division.
So the 1st table, in datasheet view, is basically:
# | Last Name | First Name | Client Code | Client Division
The second table is a list of addresses based on Client Code and Client Division:
# | Client Code | Client Division | Address 1 | Address 2 | Etc
----
What I'm trying to do is the following:
I'm trying to create a form such that as I'm typing in a last name, it does autolookup and auto filling. I may have multiple people with the Last name of Smith, so then it dynamically creates a dropdown box with all possible unique first names associated with the last name Smith. And after I choose/type in the first name, I get to two more dynamically created drop-down boxes that list all possible Client Codes and Client Divisions associated with this name. After choosing that, I figure it's a simple autolookup to populate the address fields.
Does anyone have any insight on how I can get this done? Another piece of functionality that I'm trying to work into this is that if the name doesn't exist or if there's a new Client Code and Client Division or a new address, the data gets automatically added to the respective tables.
Does anything of what I wrote make any sense?
Any help would be greatly appreciated.
thanks
Imants
I am trying to pull information from 2 seperate tables and store that information in a 3rd table. I need to autofill information from both tables when I select the information from the field that joins them. Then that information needs to be saved in the 3rd table. I have to store this "duplicate" copy of the informaion becuase one table's inforamtion changes quite frequently and I needs to be able to find historical data. Here is a simplified version of my tables for example.
Products: ProductID (KEY), ProductDescription
Quote: ShipID (KEY), ProdcutID(KEY), Price (changes per ShipTo)
ProdutsOrdered: OrderID (KEY), ShipID, ProductID (KEY), ProductDescription, Price
Again, all information has to be stored in ProductsOrdered because things like Quote.Price can change frequently.
Another important feature is I have to beable to add records with what ever method I use. This Is being done in a sub form so that many products can be placed on an order form. I can get the tables to combine in a way that will allow me to view the information, but not add to it.
I have a program that runs under access 2007 that I use at my work. We will soon be updating to MS office 2010 and the program will not work now because a calender file .ocx was removed from access 2010. Is there a way to get the 2007 .ocx file to work in access 2010?The program I am using is a relatively simple stand-alone and unsupported app that we use to request patient arrival and departure from various radiology tests inside a hospital. No reports are made from the app other than the number of patient transports for the day.
The app is placed on a common drive accessed from any pc in the hospital. No special permissions are required. But our app does use the calendar, time and date functions in access 2007. When I tried the app on a pc with access 2010, it basically says it (access) cannot open the app because a .ocx file is not present.Is there a way to make the access 2010 calendar file work in access 2007?
I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.
I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?
Hi all,
I'm making a simple query to tell me how old a list of members are in a database.
I figure as I have their birthday, armed with todays date access should be able to tell me (in years only) how old they are.
Any help is appreciated
Hi,
I have 2 computers (computer 1 with Office XP and computer 2 with Office XP SP1). An MDE file created from an MDB file on computer 1 will not work on computer 2 (run time error), but an MDE file created on computer 2 also does not work on computer 1 (error message was "database corrupted").
Does anyone know why and what I can do to make the MDE work on both machines?
Hi. I have got a small database, I have split it into front and back end. I then try to make an MDE out of the front end and after going to tools>db utilities>make MDE file, i got the save as box open up and i pressd save to my desktop,the box disappears and the screen is just blank(like there is no database file has been open,at the bottom of Access it says Make MDE/ADE.
And it just crashes.. it doesnt come up with any errors.
If anyone has any information pleeeeeaaaseee help.......
My Combo Box works and displays new entries in text boxes in my form. The selection appears in every record. I would like the selection to be stored only in the displayed record and not for each and every record. Any ideas?
My combo box works on an existing query( Qry1). I would like the data from my query (Qry1) to be displayed in the record (in Form1) which I am viewing. When I go onto a new record (IN Form1) I would like to make a new selection from my query (Qry1) and then store these details in the new record in Form1.
I am a little frustrated - please help.
This seems so simple, but, being a novice, I'm scratching my head.
I'm using an unbound text box in the form footer to sum the value of a column in the form ( =Sum([LineTotal]) ). [LineTotal] evaluates correctly, yet the sum is always #ERROR. Is there something I am missing, or am I correct in thinking that this should work?
Thanks to anyone who can shed some light.
-Don
I open a form that is bound to a linked SQL table. On that form a have a list box that shows a series of records based on a query. The listbox DOES contain the PK field "Record_date" in its query.
I am expecting that if I click on a given record in the list box the form would change to that matching record. What am I doing wrong ?
List box code:
Private Sub List212_AfterUpdate()
' Find the record that matches the control.
Dim rst As Object
Set rst = Me.Recordset.Clone
rst.FindFirst "[Record_date] = #" & Format(Me![List212], "mm/dd/yyyy") & "#"
If Not rst.EOF Then Me.Bookmark = rst.Bookmark
End Sub
If I change the form record and click the list box, its always takes me back to the 1st record - not the matching record selected in the form.
Help ?
Hy,i've got three tables:
products:
product_id,product_name
months:
month_id,name_of_month
calculations:
calculations_id,month_id,amount
Also have this query:
SELECT DISTINCTROW products.product_name, Sum(calculations.amount) AS [Sum Of amount]
FROM products INNER JOIN calculations ON products.product_id=calculations.product_id
WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value)
GROUP BY products.product_name;
My problem lies in this part of query :
WHERE calculations.month_id in (forms!frmMyForm!txtMyTextbox.value).
I've got problem when i want to get values from my textbox on my form.
If i instead (forms!frmMyForm!txtMyTextbox.value) put (1,2) my query works fine,but when i wanna get this same values from textbox on my form it returns me an error.
I have also noticed one more thing:If i put in my textbox just value 1 it works fine,but if i use more values separeted by comma instead of result it returns me an error.
Why is this,and how to avoide this problem?
Thanks!
Private Sub propertypass_Click()
If [Forms]![UpdatedFullInventory]![PAM_ID] = [Tables]![UpdatedPropPassInventory]![PAM_ID] Then
MsgBox "A property pass for PAM ID " & PAM_ID & " has already been created. Please search the property pass database for more information!", vbOKOnly, "Error!"
End If
My debug message says
Microsoft can not find the field 'l' referred to in your expression.
Hello,
For osme reason this MS Access database I'm working on will not let me debug it. First off, even if I mistype a variable name it is not breaking and giving me an error message. I checked and I do have 'Option Explicit' set as well as "Error Trapping - Break on All Errors" in the Tools - Options - General.
Also, when I set a breakpoint in my code, it doesn't work either.
Any ideas as to what is going on??
Thanks,
Brian
Our database is 1.6gb in size.
It has stopped working, is there a way to delete old records?
We are told it will crash the computer, because access needs the same space as the data its self.
Any ideas?
Thank you for your time in reading this post