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'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?
Iain
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.
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...
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.
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?
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
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.
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?
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?
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
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 RelatedHi,
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.
Scott
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 Relatedhi 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
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
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
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
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).
Hello,
I wonder if anybody can help me.
I have a table called ITEM, within ITEM I have three fields ITEM NUMBER (Key Field), Item, Cost,
I have another table called INVOICE ITEMS, Within INVOICE ITEMS I have six Fields, INVOICE NUMBER, ITEM NUMBER, ITEM, UNIT COST, Amount, Total Amount.
I want to use Lookup wizard to complete the fields ITEM NUMBER, ITEM, UNIT COST from the ITEM table.
Is this possible?
Regards
Nathan
Hello, is there any way I can have my single-parameter query bring up a lookup list of options from a table field rather than relying on the user to correctly type in the criteria? Then when this query runs I will know the user is picking one of the correct options from the lookup list.
Thanks.
Hi. I'm quite poor with Access, so sorry if this sounds dumb.
I have a table of customer records (tbl_customers), including name, address & postcode etc
I have a spreadsheet of customers their postcodes from a different system that shows customers who have bought a particular product.
Is there a way that I can create a query that lists those records in tbl_customers whose postcode is matched in the spreadsheet?
Jim
Using MS Access 2002
Need some help trying to filter out some data so it can be deleted from the main table so updated data can be pasted into it that table.
Table name is "dbo_VG_PropertyValues"
AppID - Number
DNIS - Text (This is the column i need filtered)
PropertyName - Text
ProperetyValue - Text
I had a query where i would just type in the 3-4 DNIS numbers that i needed to search for by using "11111" or "22222" or... etc... in the simple query view under criteria..
I'm now getting lists of 50 + numbers i need to do searches for, delete and replace with updated data. I can't run a string of "or" statements on 50+ numbers, it won't allow it. I created a table called 'DNISList" with the 50+ numbers i need to search for out of the "dbo_VG_PropertyValues" table. In the simple query view i linked on the DNIS number from both tables and set the join property to show all the records that matched. Hey it looks good, so far, i got the results i wanted but can't delete anything.
Is there anyway in the criteria field under the column DNIS from "dbo_VG_PropertyValues" where i can so a search on that table without linking the tables? I hope I'm making sense. You know something like:
IN ([DNISList]![TFN])