I am new at creating databases, and I have the jist of creating tables. But my boss wants me to create a database that notices two fields such as (Item and Size) and when I enter and Item such as Helmet and then small for the size, he wants the (NSN-national stock number) to appear which consists of 11 digits. I viewed other forums and usually in a form in Access you enter a number and the rest of the information appears. However I need the autolookup to recognize the text and output the number. I understand that I have to create a table that contains all of the information so it has something to refer to. I am not familiar with Visual Basic, to write a program code. Second, I he doesnt want the information to appear in a form, but in a query. If anyone could please help, I would greatly appreciate it, because I am pressed for time.
I have created a 'junction' table between 2 many to many tables:
Jobs table (details all jobs)Jobparts table ('junction' table details Job ID, Part ID, quantity and unit price)Stock table (details all stock).The fields Job ID, Part ID and Unit price are each lookup fields from the corresponding fields in the jobs and Stock tables with quantity being a manual entry. This works fine.
I discovered you can automatically populate the unit price field using autolookup based on the entry of the Part ID field on the form. However having amended some pre-existing jobpart details randomly on the form when I checked back to the table they had not updated. Similarly when creating new entries while the form 'unit price automatically updated on entry of the 'Part ID' these details did not feed through into the table.
I assume it has to do with how the table was originally set up the field properties will need to be changed.
Haven't touched Access in quite some time and now I can do a simple task.
Here's what I have:
2 Tables
Table 1 includes 2 fields COURSE CODE and COURSE NAME
Table 2 will be a list of students and the courses they have taken
What I'm trying to do is as I'm entering a new student in the database I want the course name to automatically fill in when I type in the course name.
I did lookup fields for both the course code and the course name to ensure the correct codes are entered. However since the data in table 2 does not autofill there is the posibilty of having a row with a course name that does not match the course code.
How can I force table to 2 match the course name and code relationship from table 1?:confused:
I'm new to access and trying to figure out how to use a field to look up and return results from multiple tables. I've tried several books and also the northwind traders example. What i'm trying to do is just like the oder form in the orthwind traders example on the order form. I would like to insert the customer name via a combo box then have the address, phone etc. automatically update as well as automatically update the shipping address etc with the current billing address. I know this is pretty basic but I must me missing a step.
i have a user permission table.that consists of PermissionPK, UserFK, CompanyFK. I also want the username to be automatically filled in?So when a user ID is filled in on the table, it also fills in what that UserID's Username should be?As i need both the UserId and Username text for code that looks at the Environ username.
I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.
However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.
I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.
But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.
Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.
For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?
Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?
So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?
I am creating a database for a hyperthetical car hire company.
I have a field with a lookup/query. The data that this query searchs for is entered into my table/form already. Is there anyway of avoiding repeating this data twice?
I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".
Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.
What I am trying to do is run a report that identifies the total labour cost at the time of entry of the Time card. (The pay rates will be changing regularly and I need to calculate the cost with the correct payrate at the time they worked).
This is to be calculated by the number of hours stated in the tblTimeCardHours, against the employeeId from the TimeCard and retrieve the Payrate amount depending on the DateWorked in the tblTimeCard.
I have thought that I would need to use Dlookup to get the amount from the tblPayRates for the date but I am a bit stuck as I am new to this concept and not sure if this is the best way forward. I would be most grateful if someone could point me in the right direction please...
I have a table linked to a sql database, I want to run a query that will only show certain depots but not sure how. i.e weston,london I can run a query that will work if I put in a single depot but I need a query that I can have serveral depots
I am trying to write an updte query and 4 of my fields are lookup to a table called option (which is a yes/no/NA table). Can i have the yes/no/na table in my query linked to each of the 4 fields in my entry table. I have named the i.e. FDD: option, CD-ROM: option, DVD-ROM: option, CD/RW: option. But when i save the query and then go back into design view there is only one column for the option field with criteria: "yes" And "no" And "no" And "yes".
Do i need to have a seperate lookup table for each of the 4 fields in the entry table for this to work?
I have a problem in Access which is beyond my limited knowledge. I have two tables (one the result of a query and the other a fixed table).
My ultimate aim is to obtain a list of distances between two points for multiple sets of sites. The query gives me the 'a' end site location and the 'b' end site location, and the fixed table shows all the possible combinations of 'a' end to 'b' end distances. So, I have a table that shows all the possible distances from London to every other city, and from Birmingham to every other city etc etc and am looking to see how far it is between the specific sites in returned in my query table.
In excel I would use a Vlookup table on a single instance, but I am not sure how to translate this into a query that combines all the variables.
In logical form I am looking for something that says:- if query field a = table field a AND query field b = table field a then return table field c.
There are multiple occurrences of data within table fields a and b, so a join won't work according to my limited knowledge of joins.
I don't think I'm explaining very well, so please feel free to probe for further detail, but I would really welcome any suggestions.
Hi Folks I have a form that is based on a query. I want to change the unique ID that is displayed on this form. The new ID is sourced from a different query. I.E. Not the same query that the form derives its information.
I have set the data source property of the field (Combo1) to the "other" query.
How do I get the Combo1 to update to the value of the first record? Currently its defaulting to Null.
I have tried using : Me.Combo1.Requery - This does NOTHING to the vaue. Me.Combo1.MoveNext - I keep getting a "Method or data members not found" error.
I'm trying to get a report based on a parameter query to work. The report is to view all contacts on the database who have been assigned with a particular "Contact Type" - i.e. client, supplier, volunter etc.
These Contact Types are themselves sourced from a LookUp table. Unfortunately this means that when I run the parameter query it doesn't recognise any contacts as being "client" or "supplier" etc, but does display them when I enter the ID number which has been assigned to those Contact Types - this means that unless I know all "clients" have the ID of "5" etc., the parameter query won't work.
Is there a way around this, which doesn't involve me having to make big changes to the LookUp table and associated relationships?
Issue, Review, and Information. These 3 fields get their data from the same lookup table that holds some code values.
So I'm trying to create a query to export to excel that shows the data for this table, but for the lookup fields, I just get the ID's from the lookup table.
I usually code in .net or asp, but am building a system in pure access.
I want to do the following from a textbox on a form....
1) Lookup all values in a table that match textbox.value * 2) lookup all values that matach * textbox.value *
I want to display the values that match textbox.value * first
So far I worte a query that has a parameter of Like ([Forms]![formname]![txtname]+"*") Yet I'm unsure as to how to get the query to display the ressults in the format I want... can someone help please?
Hi, I have a report by department that the user needs to enter in the department name to produce the report(Parm.Query). I have tried to make it somewhat easier via wildcards but if they still don't know exactly how the data is stored, they will have a hard time. Example: Labor and Delivery is stored in the table as L&D. Is there a way to use a dropdown/lookup to select the department to run generate report?? I know this is very basic but I am having a "No brain Day" Thanks in advance!!!! Rick
I have a table of 'things' and two related tables, type and subtype. Each thing has a type and a subtype. The types table contains simply Index (autonumber) and Type (text). The Subtype table Index (autonumber), type (number) and subtype (text), where index and type form the primary key and type is a foreign key to the types table. Thus each type has its own sub-set of subtypes. In the design of the Things table I have set the lookup for Type and Subtype to be a dropdown, but of course the subtype drop-down shows all subtypes, not just valid ones. If I select a subtype that is incorrect for the selected type then of course I get an error, but it would be nice to set the query in the subtype lookup's Row Source to just display the valid subtypes. At the moment it just says "SELECT index, type FROM subtype" How do I put in a WHERE clause that references the Type field for the current row: "SELECT index, type FROM subtype WHERE type=<type selected in current row>"?
SELECT Loans.CustomerID, Loans.LoanID, Loans.LoanAmount, Loans.StartDate, Loans.EndDate, Loans.LoanLender FROM Loans WHERE (((Loans.StartDate)>DateAdd("d",-32,Date() And ((Loans.EndDate)>DateAdd("y",-1,Date())))));
I want my query to do a monthy lookup of bussiness where a loan is either opened or closed in the last 32 days. This works except its pulling up records from all previous years. I tried to filter the year as shown above, of course its not working. So what do i have to do? Thanks for any help in advanced.
