Hi Gurus,
Here's my situation. I have three columns in Access:
Attribute Name, DateValue, CharValue.
I wish to create a make table query which does the following:
If Attribute Name = 'approved_date' then take the value from DateValue Column and populate it in a New table 'new'.
but if the
Attribute Name = 'description' the take the value from CharValue column and populate it in the same table 'new' in a different column. Please help, how to achieve this.
I have a table containing about 120 records of 40 fields containing integer values. The values are 0 (for 'no experience'), 1 - 5 (for evaluation of experience) and 9 (for question not answered). I would like to generate a row of averages for the 40 columns.
Access includes the '0's when using the Avg function. (So 1,0,3,0,1,4 yields 1.5 (1+0+3+0+1+4 / 6) rather than the accurate 2.25 (1+0+3+1+4 / 4)). I can tackle this in two ways: I either convert all zero's to NULLs, as Access will not count NULL in an Avg function call, or I can do each column in a seperate query using a WHERE clause. I also have the problem of screening out the 9's. I'm reluctant to create 40 queries and then another to amalgamate the results as this seems a very silly way to solve this problem. I cannot convert both the zeroes AND the 9's to NULL as to do so would lose valuable data.
Can anyone suggest how I can obtain a full row of averages for the 40 fields, ignoring 0's and 9's?
I have a table which has a column and some of them are filled and some are blank. I want to fill the blank ones with the previous values, how do I do this. Thanks for any help..
Is there a way to hide a column in a query that does not have any data in it?
I have tables that are populated with YES/NO boxes and I only want to bring back the information for fields that are YES. Currently, the queries run and bring back all fields.
Each query will only have 1 result as they are limited by the primary key.
I am making this harder than it has to be i'm sure. I had a previous post of auto-texting a field, well why can't I just take two fields I have, such as the ID field which is an autonumber field and combine that with a type field that I have which is D.
If I could merge this in a query, wouldn't it give me the results as D01, D02, etc?
I couldn't find anything helpful online, one side said do the following.
SELECT receivables2.type+receivables2.ID FROM receivables2;
Only I get errors.
I also tried this.
SELECT merge(receivables2.type; receivables2.ID); FROM receivables2;
I created a fixed header cross tab query that totals up how many photos of each size there is in an order. I wrote some visual basic code to total up the price (kind of complex with the different packages) but any orders without at least one of every size photo completely locks up Access. The following is the code:
Private Sub Text66_DblClick(Cancel As Integer) Dim Units Dim UnitsCache Dim PackagesCache Dim F4x5Cache Dim F5x7Cache Dim E8x10Cache Dim E11x14Cache Dim S16x21Cache Dim T21x24Cache Dim WalletsCache Dim T4x5 Dim T5x7 Dim T8x10 Dim T11x14 Dim T16x21 Dim T21x24 Dim TWallets
If [4x5] = Null Then T4x5 = 1 Else T4x5 = [4x5] End If
If [5x7] = Null Then T5x7 = 1 Else T5x7 = [5x7] End If
If [8x10] = Null Then T8x10 = 1 Else T8x10 = [8x10] End If
If [11x14] = Null Then T11x14 = 1 Else T11x14 = [11x14] End If
If [16x21] = Null Then T16x21 = 1 Else T16x21 = [16x21] End If
If [21x24] = Null Then T21x24 = 1 Else T12x24 = [21x24] End If
If [Wallets] = Null Then TWallets = 1 Else TWallets = [Wallets] End If
RerunPackages: PackagesCache = "x" If [T21x24Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then PackagesCache = "F" [WalletsCache] = [WalletsCache] - 2 [UnitsCache] = [UnitsCache] - 4 [T21x24Cache] = [T21x24Cache] - 1 Text58 = Text58 + 439 GoTo FoundOne End If
If [S16x21Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then PackagesCache = "E" [WalletsCache] = [WalletsCache] - 2 [UnitsCache] = [UnitsCache] - 4 [S16x21Cache] = [S16x21Cache] - 1 Text58 = Text58 + 305 GoTo FoundOne End If
If [E11x14Cache] > 0 And [WalletsCache] >= 1 And [UnitsCache] >= 3 Then PackagesCache = "D" [WalletsCache] = [WalletsCache] - 1 [UnitsCache] = [UnitsCache] - 3 [E11x14Cache] = [E11x14Cache] - 1 Text58 = Text58 + 236 GoTo FoundOne End If
If [WalletsCache] >= 2 And [UnitsCache] >= 4 Then PackagesCache = "C" [WalletsCache] = [WalletsCache] - 2 [UnitsCache] = [UnitsCache] - 4 Text58 = Text58 + 169 GoTo FoundOne End If
If [WalletsCache] >= 1 And [UnitsCache] >= 3 Then PackagesCache = "B" [WalletsCache] = [WalletsCache] - 1 [UnitsCache] = [UnitsCache] - 3 Text58 = Text58 + 127 GoTo FoundOne End If
If [WalletsCache] >= 1 And [UnitsCache] >= 2 Then PackagesCache = "A" [WalletsCache] = [WalletsCache] - 1 [UnitsCache] = [UnitsCache] - 2 Text58 = Text58 + 89 GoTo FoundOne End If
FoundOne: If PackagesCache <> "x" Then If Text56 = " " Then Text56 = PackagesCache GoTo RerunPackages ElseIf Text54 = " " Then Text54 = PackagesCache GoTo RerunPackages ElseIf Text52 = " " Then Text52 = PackagesCache GoTo RerunPackages End If End If
UnitsCache = (T8x10 + T5x7 2 + T4x5 4) - UnitsCache Do If F4x5Cache < 4 Or UnitsCache = 0 Then GoTo Skip4x5 End If F4x5Cache = F4x5Cache - 4 UnitsCache = UnitsCache - 1 Loop Skip4x5: Do If F5x7Cache < 2 Or UnitsCache = 0 Then GoTo Skip5x7 End If F5x7Cache = F5x7Cache - 2 UnitsCache = UnitsCache - 1 Loop Skip5x7: Do If E8x10Cache < 1 Or UnitsCache = 0 Then GoTo Skip8x10 End If E8x10Cache = E8x10Cache - 1 UnitsCache = UnitsCache - 1 Loop Skip8x10:
Text42 = 0 Redo: If WalletsCache >= 5 Then Text42 = [Text42] + 40 WalletsCache = [WalletsCache] - 5 GoTo Redo End If If WalletsCache = 4 Then Text42 = [Text42] + 37 ElseIf WalletsCache = 3 Then Text42 = [Text42] + 33 ElseIf WalletsCache = 2 Then Text42 = [Text42] + 28 ElseIf WalletsCache = 1 Then Text42 = [Text42] + 18 End If
I have had a look through the forum and I am not sure if this can be done. I have a table. i.e. with columns Decsription 1, Cost 1, Description 2, Cost 2 Description 3, Cost 3 description 4, cost 4 etc.
I am looking to try and gave a combo box show this information as follows. I.e
I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
In Access 2010 I have a Data Entry Form on which I have an unbound textbox in the header that the user can put a default date in. In the body of the form is a bound textbox that records the date and the default value is set as =defaultdatestat (obviously the name of the box in the header).
Problem: The default date shows up perfectly until a value is put in any of the other text boxes.
For further info : If you put values in text boxes default value disappears; if you then push escape the default value reappears when the values in the text boxes disappear.
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN) SET TableA.HCC = TableB.HCC WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL ORDER BY TableB.HCC, TableA.CDN;
I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:
Private Sub Test1_AfterUpdate() If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then Me.Test1Result2.RowSourceType = "Value List" Me.Test1Result3.RowSourceType = "Value List"
I'd like to create a query which will consist of simple SELECT statements as follows:
SELECT [table1].[field1], [table2].[field1], [table2].[field2] FROM table1 INNER JOIN table2 ON ([table1].[fieldX] = [table2].[fieldX]);
The challenge arises b/c instead of joining on equal values, such as the following: [table1] INNER JOIN [table2] ON [table1].[field1] = [table2].[field1]
I would like to join based on equivalencies, such as: [table1] INNER JOIN [table2] ON [table1].[field1] = 34 is equivalent to [table2].[field1] = 2;
I do not know the proper syntax, so this is where I need help. I tried to search online without any success.
I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:
Code:
AcctNum FoodType FoodClass ------- -------- --------- A123 Apple Fruit A123 Apple Fruit A123 Grape Fruit A456 Potato Vegetable A456 Potato Perishable A789 Carrot Vegetable A001 Banana Fruit
For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:
It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').
I have a travel time database that should tally the number of minutes traveled and convert them into a time (hours and minutes). I, however, am having difficulty converting the numeric values cleanly. Is there anyway to convert 102 minutes + 100 minutes + 110 minutes = 312 minutes to 5 hours and 12 minutes cleanly? I need to take averages of time traveled and hours worked but cannot do this correctly. Thank you for any help in advance.:eek:
There is a text box in a form that user can enter the value. How can I check if the value entered by user is already existed in the record of the linked table or not? If the value (or record) do not exist, that new value/record will be added to the table. If the value do exist, just show the msgbox to indicate.
I have a lookup value in a table, and I would like to remove all values of the lookup value from vba. How can i do it via vba? i've been trying to do it in a query but i have always some errors and i can not remove them.
i have one table in which ID is Primary ID with Different Values
Like
ID NAME PAN 1 A X 1 B Y 1 A X 2 C Z 2 C G 3 D U
it shows that ID 1 having 2 Name (A& B,with PAN, X & Y ,respectively).how can i get this that ID having More than 1 Value like 1 and how can i select only these records ID which having more than 1 value and how can i update values for 1 ID.
Firstly, is it possible to hide unchecked values in a listbox? I have a user with several roles and I want to only show the ticked roles in the listbox.
Secondly, can you create a hyperlink on listbox values? i.e, if I click on "Manager" in the roles listbox, it follows that to another form and opens the record about managers?
I need for Access only to sum the "hours worked" of my payroll data, only if another field, "pay code" meets a certain criteria.
In Payroll, some codes are used to designate actual hours worked like REG (Regular Hours) and like OTS (Overtime Straight) while other codes are added as "premiums" to these hours and do not mean that you actually worked those hours, like OTP and HOD (Overtime Premium & Holiday Premium). The hours are only added as a reference to the actual hours worked they are attached to. Their units should not be counted as hours worked.
I want Access to 1) Group By Cost Center, 2) Sum "Hours Paid" that are tied to a REG or OTS (only), but 3)Sum the Earnings for all pay types.
Basically, (in excel-like terms: Hours Paid Column= SUMIF of Paycode = REG OR OTS. But Earnings Amount Column= Sum of all Paycodes)
Cost Center Job Type Hours Paid Pay Code Earnings Amount
