I have a form presented in datasheet layout.
I set the initial sort by two columns, but I would like to keep highest priority for that initial sort, so when user wants to sort by some other columns that sort happens inside my initial sort.
I have a database where part of it consists of an Item table and a Formula table. There is a many-to-many relationship between them so I created a junction table for this purpose.
Tables
tblItem
tblFormula
tblFormula_Item_JNT
There is a column in tblFormula called [Priority] that is a lookup field. It has the values of "High", "Medium", "Low".
So here's what I'm trying to do: I have a query that searches against tblItem for all Items that have a value of No/False for a field called Item_Status. That's simple enough. But I also want to show the Priority of the Formula that the Item belongs to. When I add that to my initial query I get multiple results of the same Item if it belongs to more than one Formula, which I understand. But I really want an Item to show up only once.
I want the query to go through each Item (based on its Item_ID), look through all the Formulas it's in, and if at least one of those Formulas has a Priority of "High" then it will display "High" in a field (could be a calculated field) in the query results. If there are no "High" Priority Formulas, then look for if there are any "Medium" Priority Formulas, etc.
I have a form I created in the past that auto-fills some of its fields based on the user's selection of a record from a combo box linked to another table. The form has been working perfectly however I recently tried to add a couple of more fields to the form that need to be autofilled. I added the new fields to the source table and added columns to the combobox that should allow my VB code to fill those boxes. However only one of the new fields is filling correctly. After trying a few different things I notice that the problem seems to be with whatever data field becomes the 21st column in the combobox. Is there some sort of limit on the number of columns contained in the data source for such a box?
Option Compare Database
Private Sub EmpIDAutofill_Change()
Me.EmployerCatAutofill.Value = Me.EmpIDAutofill.Column(1)
Me.StreetNumAutofill.Value = Me.EmpIDAutofill.Column(2)
Me.JobCityAutofill.Value = Me.EmpIDAutofill.Column(3)
[code]...
Okay, my first problem is solved...Access now stops me from entering a phone number that is already entered...so I'm a happy camper...thanks again to those who helped :)
now I have my next problem, I have 8 columns, each column has phone numbers and I've separated them into what the first digit of the phone number starts with...so columns 2 to 9...
now I'd like to sort each individual column so that it puts the phone numbers in numerical order...the only option I find for sort sorts all 9 columns as if they were one column and then everything is out of whack and mixed together...sorting like that works fine in excel...what's the trick to get Access to do the same thing?? Thanks a bunch :) Rosey :confused:
I am trying to sort by 2 columns
1st column by "type"
2cnd column by " stock number"
I have the query set to ascending in both fields but it will only do the type columnexample below shows 5096B00028 not together
Quote:
requistion items stock number Material type: 5096B000335 9mm x 33.5mm winding core 5mm A/F hex Polypropylene Adstif HA840R Core 5096B00028 9mm x 28mm winding core 5mm A/F hex Polypropylene Adstif HA840R Core 5096B00114 9mm x 114mm winding core 5mm A/F hex Rynite 935 (to dwg 9020) Core 5096B000675 9mm x 67.5mm winding core 5mm A/F hex Polypropylene Adstif HA840R Core 5096B00057 9mm x 57mm winding core 5mm A/F hex Polypropylene Adstif HA840R Core 5096B000355 9mm x 35.5mm winding core 5mm A/F hex Polypropylene Adstif HA840R Core 5096B00028 9mm x 28mm winding core 5mm A/F hex Polypropylene Adstif HA840R Core 5096B000335 9mm x 33.5mm winding core 5mm A/F hex Polypropylene Adstif HA840R Core 5096B00049 9mm x 49mm winding core 5mm A/F hex Polypropylene Adstif HA840R Core
I run duplicate check on a form to make sure the file name doesn't match one that's previously been entered. Currently I have:
If Me.NewRecord Then
If Not IsNull(DLookup("File", "tblFileProcessing", "[File]=""" _
& Me![File] & """" & " and [FileClientID] = " & Me![FileClientID])) Then
DoCmd.OpenForm "frmDuplicateFiles", acNormal, , , , acDialog
End If
End If
However we've recently changed the way we work and have now added a unique ID number prefixed to the file name: 567_File_Name_1 How do I run the DLook up but parse off the digits prior to the first underscore and only check on the remainder of the file name?
I have a form set up so that a supplier code combo box is populated with a simple SQL statement to get values from a lookup table. I have a second combo box that is populated when the supplier code is selected by the user. It works well. For example, if the user selects supplier Dog then the current record value of the Product field is displayed and the combo box shows all Dog products. So that's great.
My problem arises when the form loads. I can't convince Access to fill the Product combo box with the current products. For example, if the first record has Cat as the supplier, I want it to display the Product value for the first record and to show all the Cat products in the drop down. It isn't doing it on load or on record navigation. It does work if the user reselects the supplier.
I have tried various events.
Right now I have the following code in the AfterUpdate event of cboSupplier:
qry = "SELECT SupplierCodeProductCodeEquityTypeQuery.Product.Nam e, SupplierCodeProductCodeEquityTypeQuery.ProductCode ,
SupplierCodeProductCodeEquityTypeQuery.SupplierCod e, "
qry = qry & "SupplierCodeProductCodeEquityTypeQuery.Supplier.N ame, SupplierCodeProductCodeEquityTypeQuery.ProductId FROM SupplierCodeProductCodeEquityTypeQuery "
qry = qry & "WHERE SupplierCodeProductCodeEquityTypeQuery.Supplier.Na me = '" & cboSupplier.SelText & "';"
Me.cboProduct3.RowSource = qry
This is a query, report and vba question. I'm using Ms Access 2007.
TABLE 1: projectname, activityname, totalhoursworked, employeename
TABLE 2: employeename, employeelevel
TABLE 3: employeelevel, rate
I created a select query to join the info that I need.
SELECT QUERY 1: projectname, activityname, employeename, totalhoursworked, rate, cost (calculated field (totalhoursworked*rate))
I have 2 crosstab queries.
CROSSTAB QRY 1: ROW (projectname, activityname) COLUMN (employeename) VALUE (totalhoursworked (summed))
CROSSTAB QRY 2: ROW (projectname, activityname) COLUMN (employeename) VALUE (cost (summed))
I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).
SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)
It gives me this:
However, I want it like this:
Those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.
My question is simple: I've got a database of my book collection. If i view it in a style sheet, it is something like
Author Title etc.
A 0
C 1
B 3
F 2
D 5
D 4
A 8
F 9
I would like to order the Author and Title columns (permanently). Particularly, I would like to order in alphabetical order all the authors and, if I've got the same author, order his/her book alphabetically, something like:
Author Title etc.
A 0
A 8
B 3
C 1
D 4
D 5
F 9
How can I achieve this?
I want to have a report card where I can have attendance embedded in for each students and also the highest score for each class each session.
View 4 Replies View RelatedWhen I open a Form, it appears in the proper size. Then, I go and open a Report, which is a DoCmd.Maximum on open. This works well. The report is enlarged. Now, when I go to open a form, the size of the form is max.
I tried Borderline dialog, fit window, DoCmd.MoveSize, and a couple of other things, but I cannot get those forms to keep their original size-they blow up!
How can this be accomplished?
When I open a Form, it appears in the proper size. Then, I go and open a Report, which is a DoCmd.Maximum on open. This works well. The report is enlarged. Now, when I go to open a form, the size of the form is max.
I tried Borderline dialog, fit window, DoCmd.MoveSize, and a couple of other things, but I cannot get those forms to keep their original size-they blow up!
How can this be accomplished?
I know how to setup a Replica's Priority, but where do I go if I want to change it afterwards? I have searched everywhere, or am I just supposed to create another replica and set it then?
View 2 Replies View RelatedI found this from different fourm by searching google. This is exactly what I need to do and the answer was given; however, I can't figure it out. Can someone please post a step by step instruction on how to complete the following.
Thank you!
Question
I have a datbase full of projects that must be ranked in priority order. This was simple enough but when I add a project I will likely need to change the priority rank of all the "lesser" projects. With over 500 projects adding a new project with a rank of 50 means changing all the ranks 50 and above. I want to be able to add a project and when I determine its rank I insert the rank number and every rank equal to the project just added is the indexed by 1 digit. No two project can be ranks the same, one is always more important than another.
Answer
Create an update query that sets the priority field to:
[Priority] + 1
then set the criteria to
>= new number
Where new number is the rank of the new number.
This will increment by one each priority.
Hope this helps,
Scott<>
I have been asked if I can add a priority list to my database and I am trying to figure out how to do it.
The database already exists and this would be added code/forms. Basically this database has a number of users, all identified by a login name. Each of these users have several projects assigned to them. The projects are identified by a unique project number.
What I need to do is to be able to list all the projects that a user has, which is easy to do by simply creating a query, but then the user needs to be able to type in a priority number (1 to infinity) for each of these projects with no number repetitions. If a project priority is updated, say from 6 to 2, then the projects that had priorities 2 to 5, get a +1 added to their priority number so they go 3 to 6. Likewise if a project gets "demoted" (priority is lowered) then the ones below go up one.
I have a very basic application to manage the tasks. In the main form "Tasks" one of the field is the priority (Order by) and I have this example:
Task Priority
B 1
A 2
C 3
E 4
D 5
If the user change one priority, I need a code to automatically change the others too respecting the new priority order. For example if the user change the priority 3 to 1 for the task C, the other numbers have to change to become:
Task Priority
C 1
B 2
A 3
E 4
D 5
There is a way to do this?
I have created an access application for production system .
I have a final stock query and 2 to 3 orders based on priority and i want to create a shortage list based on priority ... here is detail :
ItemName....|Stock qty....|ORDER1....|ORDER2....|ORDER3....|
A.................|200............|40............. |90............|100...........|
B.................|500............|0.............. .|400..........|200...........|
C.................|30..............|40............ .|0..............|20............|
D.................|0...............|0............. ..|10............|0...............|
E.................|900............|400...........| 300..........|280...........|
F.................|130............|200...........| 50............|0...............|
Now I want shortage as:
ITEM NAME |O1 SHORTAGE | O2 SHORTAGE | O3 SHORTAGE|
A................|00..................|00......... ..........|30..................|
B................|00..................|00......... ..........|100.................|
C................|10..................|00......... ..........|20...................|
D................|00..................|10......... ..........|00...................|
E................|00..................|00......... ..........|80...................|
F................|70..................|50......... ...........|00...................|
What method Do I use to write a query as I have hundreds of Products and 40-50 orders????????
How do i trim a name in a string.
I have 2 rows with firstname in one and the second has surname. if i wanted to view the results to have A Einstein rather than Albert Einstein, how would this be done?
thanks.
(I am a notive in database calculations) I have a human resources database with a report that calculates how long a person has been employed and calculates vacation and sick time based on the number of years employed. Is there a way to take the single highest value in vacation time text box and multiply it by a predetermined number? (i.e., if 5 is the highest number in the list for the person, multiply it by 24)
Thanks in advance.
question,
how do i select the highest value of a column in a MS ACCESS database with a sql statement???
thnx
hi, im a bit stuck. i am creating a mail merge and i want access to retrieve only the first character of the forename to input into the mail merge.
e.g. Dear Mr. R Price.
I want the 'R' to be retrieved by access from the full forename, so 'R' would be retrieved from the name Robert in the forename field.
Thx
I have table where First Name, Last Name and Initial are all in one field. What is the syntax to remove initial? Been playing with Right and Len with no success, I'm sure this has been done before.
View 14 Replies View RelatedI have 2 Comboboxes, one for Region and one for Country, and am attempting to initalize/cascade them. Here is the basic info for the the underlying tables:
tblRegion
regionAbbreviation (Text, PK)
regionName (Text)
regionOverseer (Text, Foreign Key...essentially a parameter)
[code]...
Some relational constraints of which to be aware:
1. Every Region has at least one Country
2. Every Region has one and only one Overseer
3. An Overseer may be responsible for more than one Region
Characteristics of the Comboboxes:
1. Both use 2 columns with the Name field (first column) being displayed and the Abbreviation or Code field (second column) being the bound column.
2. The nominal case is that both comboboxes will have multiple items to select and in that case the comboboxes are initialed with a query containing a UNION that inserts the string "<ALL>" (same string in both columns).
3. When the case arises that an Overseer manages a single Region or a Region contains a single Country, the Union query is bypassed such that the "<ALL>" string is excluded.
The comboboxes are used to create a filter condition to drill down into a potentially very large recordset.Now to the issue at hand ...
The Region combobox is initialized first. The initialization is accomplished by setting the RowSource property to an appropriate query. The first entry in the item list is then loaded to the combobox Value property by the following line of code:
Me.cbxRegion.Value = Me.cbxRegion.Column(0,0)
This always works whether there are multiple items or just one. If there is just one I set the Enabled property to FALSE; no point in reselecting the only item avaliable.The Country combobox is then initialized in a similar manner using appropriate code that is virtually identical to that used for the Region combobox.The problem I am having is that if there is only a single Country, the Name is not displayed in the combobox but is contained in the selection list. Note that i do not have a problem with the Region combobox when it is initialized with a single Region.
I have searched this forum and others and found similar inquiries and a couple of possible solutions; some clearly don't work while others appear to but result in run-time errors later in the execution of the code (run-time error '2115').I do not have a simply test case I can upload at this time; also new to the forum and appear to be restricted as to my ability to perform uploads.
I want access to generate for me an autonumber, but with the current year as initial value. ( To give new hired employees a unique number). For example, we are now in 2013
If there a new hire, that employee is going to get a unique number 2013001
The next new hire, will get 2013002 and so on
I know how to append a table to set the initial value, but this means, that next year I will have to append a new table again to set the initial value to 2014.
I want access to do this automatically for me, so that next year a new hire will get the unique number of 2014001, the next one will be 2014002 etc...
I have 3 fields in query. I want the highest value from field2 in field3. Shown as below: How I do this without using Group By?
field1 field2 field3
1 1 2
1 2 2
2 1 3
2 2 3
2 3 3
Hello,
I am looking for a query to select the second highest value (in this case a date) from a table.
For example, if I had the following dates:
9/1/06
9/2/06
9/3/06
9/4/06
I would want the results of my query to be 9/3/06.
My table is called tblEquityFunds and the field I'm looking to select is called Added. I have the following query, which I think works, but it seems like an awfully goofy way to do it:
SELECT DISTINCT TOP 2 Max(tblEquityFunds.Added) AS MaxOfAdded
FROM tblEquityFunds
WHERE tblEquityFunds.Added Not In (Select Max(Added) FROM tblEquityFunds))
Is there a more elegant solution?