Parm Query/Report Lookup
Dec 15, 2005
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
View Replies
ADVERTISEMENT
Nov 9, 2006
i have a qry_Actual_Cost_Thru whose results changed weekly.
i want to use this weekly changing result (it is a date format) in another query called qry_Current_SOPC.
i have a field in the qry_Current_SOPC called "Current Week Ending" with parmaters set on it.
i've tried setting the field to be "Current Week Ending: ([qry_Actual_Cost_Thru].[Fiscal Week])" but it still requires me to populate the date after a parm box pulls up.
how do i set the Parm to be the results from the qry_Actual_Cost_Thru without manually typing them in?
View 1 Replies
View Related
Aug 14, 2006
I have a lookup field in my table - when I try to create my report, it lists all of the data in the lookup list - I just want to display the one that is stored in the table.
How do I do this?
View 3 Replies
View Related
Mar 29, 2008
In my db the look up fields show up as numbers.
for example i have a look up field called "company" and i have two companies in it (Reddot & Thingz).
But on report they come up as 1 and 2 instead of their names.
The db is attached.
21343
If anyone knows the solution, please help me.
Thanks!
View 10 Replies
View Related
Feb 11, 2015
In our Student Administration database, we have a Student Evaluation Report which prints a 1 page per student report in memo like format. The instructor had previously input rating numbers (4=Best, 1=Worst) for 9 categories for each student via a database form into the Evaluations Table.
Two other elements of the rating are an Attendance based on number of days absent and a Grade Point Average (GPA) calculated from the student's test scores. The instructor then prints and reviews the report with each student.
There is an Evaluations Parameter table which has the following fields:
Field
Description
ID
The autonum key field.
Class
eg., 2015-1
Evaluation Number
A single digit (eg., 1, 2). There may be more than 1 evaluation for each class.
EffectiveDate
The "as of" date of the evaluation.
There are then several other tables that are input to a query that will be the record source for the report:
Table
Purpose/Data
Students
Student Name, Class
Absence
Has a record for each student's absence with date and a 1 or .5 indicating a whole or half day absent.
Test Grades
Has a record with each student's test results with date and score.
Evaluations
Holds the rating score for each of the 9 rating categories.
The Student Evaluation report is launched from an unbound Reports menu form via a button. On the Reports menu form I wish to have controls for the user to select which evaluation to report on. The Absence and Test Grade information needs to include records that are <= the EffectiveDate in the Evaluation Parameter table. The Evaluations records need to match the Class and Evaluation Number in the Evaluations Parameter table.
I would like the user to be able to select the Evaluation Parameter via a combo box vs. specifying the Class, Evaluation Number and Effective Date separately and then have the query record source for the report use those two fields as criteria. But my limited understanding of the Combo box indicates that only 1 field from the lookup query of the Evaluations Parameter table can be stored in the control whereas I need 3 (Class Evaluation Number and EffectiveDate).
How the user can select the desired Evaluation Parameter record on the Reports menu form and use the 3 fields from the selected record as criteria in the report's record source query.
View 10 Replies
View Related
Jul 24, 2007
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?
View 4 Replies
View Related
Nov 28, 2006
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?
Iain
View 2 Replies
View Related
Jul 27, 2015
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.
View 4 Replies
View Related
May 24, 2005
I am trying to work out the total labour cost of employees work.
I have the following tables that holds the information.
(Sample of field names relevant to this problem)
The relevant tables are as follows;
tblTimeCard
-TimeCardId
-EmployeeId
-DateWorked
tblTimeCardHours
-TimeCardId
- Task
-Hours
tblPayRates
-EmployeeId
-DateFrom
-DateTo
-PayRate
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...
View 3 Replies
View Related
Oct 31, 2006
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
View 1 Replies
View Related
Jan 15, 2007
hi all,
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?
cheers,
spinkung.
View 1 Replies
View Related
Sep 10, 2007
Hi,
I have a table which holds various time stamps for a booking in process.
I'd like to create a query that only picks up a specific timestamp if another timestamp has not been updated
For example -
5 15:31
8 15:37
I'd like to only lookup timestamp 8 when timestamp 5 is not in the table
How would i go about doing this?
View 2 Replies
View Related
Mar 28, 2008
Hi All,
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.
Many thanks,
Jason
View 7 Replies
View Related
Sep 14, 2006
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.
View 7 Replies
View Related
Jun 15, 2007
Hello,
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?
View 5 Replies
View Related
Feb 11, 2008
Hi,
I have a table that has 3 columns.
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.
So the table is like this now
ID...Issue...Information....Review
1.......2..........5...............1
2.......1..........12..............6
So I would like to be able to display what the ID numbers actually are, but having trouble getting a query to do this for all 3 fields.
I can run a query that shows 1 field's but not all 3.
what I would like to see
ID.....Issue...Information....Review
1.......2a.........4c.............e5
2.......1a.........3c.............2F
all 3 columns use code_ID from the lookup table.
Is there a way to do this? or should I have used 3 different lookup tables for these?
View 13 Replies
View Related
Oct 26, 2005
Hi there,
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?
M3ckon
View 2 Replies
View Related
Aug 20, 2004
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.
View 1 Replies
View Related
Sep 9, 2005
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>"?
View 3 Replies
View Related
Sep 18, 2005
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.
Scott
View 7 Replies
View Related
Jul 18, 2007
I have a Combo Box based on a table list on a Form. I want to choose several items from the list to feed a Lookup Query. Can this be done? :rolleyes:
View 1 Replies
View Related
Feb 5, 2008
hi there,
I've got Departments (id, gameId, managerId, name) table and Characters(id, gameId, name) table
I'm trying to create a lookup query for Departments which will display a combo box of available Characters to set as manager
I came up with this:
SELECT DISTINCT Characters.name, Characters.role, Characters.id FROM Characters, Departments WHERE Characters.gameId=Departments.gameId;
but it dosnt work as intended. What I need to do is take gameId from current row in Departments table and pass it as a parameter to the query
SELECT DISTINCT Characters.name, Characters.role, Characters.id FROM Characters, Departments WHERE Characters.gameId=[currentRowGameId];
any hint how to achieve this?
cheers
View 3 Replies
View Related
Feb 29, 2008
Hi,
Is it possible to create a lookup in a query against a table which has not relationship but has a match?
E.g. iif(spec(in query)=Spec (match table) then bring back cost in (match table)
Hope this makes sense?
Thanks
View 1 Replies
View Related
Aug 30, 2004
Hi there
I have created a simple query to subtract field B from field A and store this value in field C, however now I need the query to lookup the value from field C in the previous record and store it in field D in the current record. I'd appreciate your HELP
Regards
Boertjie
View 3 Replies
View Related
Oct 11, 2006
I am building a mortgage/amortization database and I need to look up the "points paid" from another table and also the "adjustment to margin" based on the points paid and the index, "MTA" or "COFI". I have no idea how or where to write this lookup. Any suggestions?
Thank you!
KellyJo
View 1 Replies
View Related
Jun 16, 2013
What I am wanting to do is to have columns at the end of my imported data that reference another table to tell me based on the queue what service type and centre it relates to.
I have attached 2 pictures one with table 1 and the other with table 2. Table one shows the last column 'SERVICE_TYPE' which I need to lookup from the reference table (aka table 2 - which has been attached).
View 3 Replies
View Related
