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 have a table with the following 4 fields (these are the ones i'm having the issue with but there are others).
ID, Country, City, Date
I enter data into this table via a form
The ID is an auto number. The date is simply enetered.
The Country Field is a lookup (in the table itself) with the following lookup properties
Display Control: Combo Box
Row Source Type: Value List
Row Source: "England";"Spain";"France" etc
Now the problem...
I also want a lookup in the City combobox (on the form) which changes to reflect what was selected in the Country field.
E.G
If in the Country Field Spain is selected then in the City Field I should have the option of selecting Barcelona, Madrid, Valencia etc.
I was told to use the following code in the afterupadate of "parent combobox":
Private Sub ComboCountry_AfterUpdate()
Select Case Me.ComboCity
Case "Test"
Me.ComboCity.RowSource = "A;B;C"
End Select
End Sub
Ive tried it but as always i get an error when i use the country drop down...
A pop up box with:
Compile error:
Method or data member not found
and visual basic opens with the top line of the code "Private Sub Country_AfterUpdate()" highlighted in yellow, and the ".RowSource =" is highlighted in blue.
These are the properties of my form
My Country Combobox is called - "Country"
My City Combobox is called - "City"
I have put the above code in the after update of the "Country" combobox as below:
Private Sub Country_AfterUpdate()
Select Case Me.City
Case "England"
Me.City.RowSource = "London;Manchester;Leeds"
End Select
End Sub
Anyone know where the error in the code is? Obviously i have reduced the code to include only one case.
Attached is the DB
Hi,
I have a table with the following 3 fields (these are the ones i'm having the issue with but there are others).
ID, Country, City
The ID is an auto number.
The Country Field is a lookup with the following lookup properties
Display Control: Combo Box
Row Source Type: Value List
Row Source: "England";"Spain";"France" etc
:confused: Now the problem...
I also want a lookup in the City field which changes to reflect what was selected in the Country field.
E.G
If in the Country Field Spain is selected then in the City Field I should have the option of selecting Barcelona, Madrid, Valencia etc.
Is this possible in the lookup of a table???
Thanks. :)
Hi there, please excuse my poor access skills, I've searched the forums but can't really find what I'm looking for......I have two basic tables in my db, one is an inventory listing all the stock in the company, and this is linked to a tracking table, which contains all the different movements for this stock item......this is a simple one to many relationship, what I want to do is lookup the value of the 'In/Out' field in the last tracking record for every item, and set the quantity of that item in the inventory table to 0 or 1 depending on the value......how would i best go about this and where should i put the code.....? Any help is much appreciated.
View 3 Replies View RelatedI have a simple query and I am trying to total the value for the the quarter. Some of the months don't have a value so I used the NZ function IE April: NZ([Apr], 0). This put a zero value in each field so it could be added together. My Total field will only total the values if there is a entered value in each field. Example
Apr May Jun Q1
1 2 3 6 - GOOD
0 0 4 No Value - Bad
Can someone help me with this? Thanks
Brennan
Hello
I've got a form with fields for projects, type of time (normal time/overtime) and hours.
the fields are cboProjects, cboTimeType and Hrs respectively (simplified for this forum)
I have a tbo in the footer, and I want it to sum all the hours entered where the type time is normal time. So I tried putting in the following expression into the Control Source of the control for tboHrsSum as follows:
=Sum([Hrs]) WHERE [cboTimeType]=1
1 is the ID of Normal Time.
This doesn't work though - can anyone tell me what I'm doing wrong?
Thanks
I have a database with various tables containing information about students, timetabling, assignment submission dates and multiple tables with grades for various assessments. All grades are held as percentages.
In a large number of different queries / reports I want to output the grade as an item from verbose scale with 17 points (excellent first, high first etc.). I've set up a table called 17pointscale which contains fields called 17pointscale (with the verbose names), lowerlimit (number) and upperlimit (number).
I have a query in SQL (which works) to take the percentage grade from one of my grade tables AssessedWorkGrades.Grade and return the text on the 17 point scale.
SELECT AssessedWorkGrades.Grade, [17PointScale].[17PointScale]
FROM AssessedWorkGrades LEFT JOIN 17PointScale ON ([AssessedWorkGrades].[Grade]
>= [17PointScale].[LowerLimit]) AND ([AssessedWorkGrades].[Grade] <= [17PointScale].[UpperLimit]);
Is there any way of converting the SQL to a custom vba function which would enable me to use this as a lookup in a large number of queries.
I think that it should be possible to set up a function called ScaleGrade and in any query Expression: ScaleGrade(XXX) will take XXX and return the 17 point scale.
I think that AssessedWorkGrades.Grade needs to be replaced by a variable that is inputted on use of the function but am not sure how to accomplish this.
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedI'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...
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
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 there,
I have a really simple question.
I have a field in a table that contains comments (text format). I want to run a query where I can get only the records that contain the word "high" in the comments. There may be some typos so I would like records returned that also contain something like "high". High is not the only word in the comments.
Does this make sense?
Thanks,
Row
Hi guys, wondering if you could help me, I can't seem to get this right..
I have two tables in question:
Items: StyleTypes:
------ ------------
ItemID ItemType
ItemType StyleType
Style
Lots of Attributes
ItemType is a list of items (Table, Desk, Bookshelf, Chair)
Style is a type of style for the item (Circular table, Radial Desk)
I want to create the Items.Style field based upon a query of the StyleTypes. So if a user types "Desk" in Items.ItemType then the Items.Style field will be limited to only those Styles which match that selected item within the StyleTypes table.
Think thats clear enough, but my attempts have all failed, hope you guys can give me a push in the right direction, thank you!
Hi all,
I have very little knowledge of databases and even less about writing queries.
The problem:
I have a database that lists cities (such as Paris, London, New York etc) as rows and employment status (Full-time, Part-time, Unemployed etc) as columns.
I need to go through each city and record which employment status is greatest (for each city) and present it in a new column. I can do this manually but I’m sure a query can be created to automate the process.
Hope that all made sense. Can anyone suggest ideas on where to start?
Many thanks,
Carl
hi
have just designed a rather simple music database for my dad to use at home as he has large collection.
have a table with the heading"artist" and another one with "album" ...my problem is that on the table there maybe 1 or more instances with the same artist but with different albums...when i run a simple query for ..say artists starting with the letter"a" i may get about 10 cases of the same artist appearing in my query when all i want is one...(depending on if i may put in say one artist like abba having 10 albums etc)...i am entering each artist with the album each time so i may enter the artist in say 10 times....depending on how many albums etc
if you understand all that...i would be grateful if someone could help me with a simple query
alan
I have a query that returns the uncompleted jobs from a single table ( its a calls database)
all i need is query that returns the amount of uncompleted jobs onto the switchboard I.E whatshername has X amount of uncompleted jobs , i have done a search and most of the solutions are very complicated for what i want to do .....anyone any ideas ?? pls