OK, here we go...... Is there a way to convert multiple rows to a single record. For example:
Rec# Existing Data
001 | 1000
002 | 2001
003 | 3252
004 | 6573
005 | 8983
006 | 9897
Is there a way to put this all in one record, like this:
Rec# . 1st# . 2nd# . 3rd# . 4th# . 5th# . 6th#
001 | 1000 | 2001 | 3252 | 6573 | 8983 | 9897
And can you do this from the query design view?
Just curious.
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.
Thanks,
Kon
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..
View 4 Replies View RelatedIs 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.
Thanks!
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;
But it says merge is an invalid expression.
Could someone please help?
I have a form displayed as data sheet, how can I block the column so that the final user will not modify the width of the columns?
M
hi all
i am adding colums like
Col_1 + Col_2 + col_3 + ...so on
now when a field is empty/null there is error in result
how to overcome this please help
i am adding colums ( not SUM(col_1) )
thanks in advance
NMK
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 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
F4x5Cache = T4x5
F5x7Cache = T5x7
E8x10Cache = T8x10
E11x14Cache = T11x14
S16x21Cache = T16x21
T21x24Cache = T21x24
WalletsCache = TWallets 8
UnitsCache = T8x10 + T5x7 2 + T4x5 4
Text58 = 0
Text56 = " "
Text54 = " "
Text52 = " "
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
Text28 = [F4x5Cache] * 12
Text30 = [F5x7Cache] * 25
Text32 = [E8x10Cache] * 40
Text34 = [E11x14Cache] * 110
Text36 = [S16x21Cache] * 155
Text38 = [T21x24Cache] * 249
Text40 = [25x31] * 362
Text44 = [16x20 Collage] * 210
Text46 = [Hard Cover w/Poetry] * 212
Text48 = [Hard Cover Book] * 98
Text50 = [Soft Cover Book] * 68
Text66 = Text28 + Text30 + Text32 + Text34 + Text36 + Text38 + Text40 + Text42 + Text44 + Text46 + Text48 + Text50 + Text58 - Text60
End Sub
Hello
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
Colum 1 on Combo; Column 2 on Combo
Description 1 Cost 1
Description 2 Cost 2
Description 3 Cost 3
Description 4 Cost 4
I want to be able to auto fill some fields on a form based on the combo box selection.
Has anyone done this before and can it be done.
Thanks
Not sure if what I want to do is possible, or at least possible the way things are set up.
I have a massive table - c. 6 million rows. It contains data along these lines:
Plan#, Item, Price, Description, Colour, Value, Location, etc.
The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).
To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.
Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).
I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.
Any help or suggestions would be much appreciated.
Thanks!!!
I am trying to convert a table that looks like the following...
Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff
I would like to covert it to....
Customer 000462 000224
Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff
I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.
Hi Im trying to update a row this is the SQL im using
UPDATE HouseEntry
SET [Date]=(SELECT [Term Dates].Start FROM [Term Dates] WHERE [Term Dates].Term =1)
WHERE
EntryNo=40;
When I run it I get an error saying "Operation Must Be an Updatable Query"
If I physically put it in the date e.g SET [Date]=#09/15/2005# It works
so there must be a problem with my Select Statement.
Any ideas???
Cheers
Bikeboardsurf
hi
my problem is when i export data form table or query to textfile it give me repeating rows my computer is win me
help me
Hi,
refer to the (record.jpg)
the whole record is identified as " rst.RecordCount " ( red color box).
how to identify each individual rows ( blue color box)?
The following is for a successful pet grooming business.
I have a Customer Table with a Customer ID as primary key. I have a Pet Name Table with a Pet ID as primary key. One customer can have many pets. The Pet Table is linked to the Customer Table via the Cust ID. I have a Visit Table with an Auto Number for each visit. The Visit Table is linked via the Pet ID number. Each pet can have many visits. That's the problem. We have exceeded the 64,000 rows in the Visit Table. All three linked tables support all the queries, forms and reports. How can I continue beyond 64,000 visits.
Thanks, Jed
I tried to search for the answer but not luck. I know there is a max size around 2gb for a DB but is there a row limit for a table or is it based upon size?
View 4 Replies View RelatedHello there,
I am making a roster for 25 people. In my form, the start of the roster is 0700hours until 1900 hours (increments of 15 minutes each) so I have 48 check boxes, which are ticked when one is rostered on for that time.
I am getting crossed eyed looking across the screen at these 48 tick boxes each for Monday through to Friday.
I have searched high and low and cannot find anything on how to have 24 check boxes (7am to 1pm) in one row and the other 2 dozen (for the next 6 hours until 7pm) on the next row, to be less painful on the eyes.
Can this be done?
I would be grateful for any ideas on how to start.
Thank you.
Regards,
Ligaya
Hallo Everyone,
I have a small problem in query selection. I have a query that selects values based on a criteria. [value from a combobox]. The problem is that i need to select all the rows if the value of the combobox is empty.
aravind.s
I hope this is a simple query becuase my brain has drawn a blank on it.
I have a query with a parameter which a form textbox. If the text box has X value I want to use one list of parameters and if has Y I want to list all rows in the query.
Any help appreciated!
Dave
Hello, Does anyone have any suggestions on how to delete certain rows? For example rows 10,000 to 17,000. I know in MS SQL server you can use Rowcount, but I believe there is no Rowcount in Access?
View 1 Replies View RelatedHi all,
Is there a way i can transform through a query or vba?
A | B | C
701 70-6
701 70-7
701 70-9
702 70-1
702 70-3
..... etc
transform it to
701 | 70-6 | 70-7 | 70-9
702 | 70-1 | 70-3
703 ....
Thanks in advance
I have what is probably a simple thing to do in a qry but I just can't figure it out.
I have a spreadsheet that I imported into access. The basis of this spreadsheet is just basic company info.
Company|Address|City|State|Zip|Name
AAA Comp|123 Street|Anywhere|CA|90210|Mr. Somebody
ABC Comp|345 Street|Anywhere|CA|90210|Ms. Somebody
ABC Comp|345 Street|Anywhere|CA|90210|Ms. Anybody
ABC Comp|345 Street|Anywhere|CA|90210|Mr. Anywho
XYZ Comp|678 Street|Anywhere|CA|90210|Mr. Whosthat
For some companies, the information will repeat, like ABC Comp. What I need to do is to combine the rows with the same company information ie:
Company|Address|City|State|Zip|Name
AAA Comp|123 Street|Anywhere|CA|90210|Mr. Somebody
ABC Comp|345 Street|Anywhere|CA|90210|Ms. Somebody, Ms. Anybody, Mr. Anywho
XYZ Comp|678 Street|Anywhere|CA|90210|Mr. Whosthat|Pres.
Is there a way to do this in a qry?
Thanks in advance!
I have spent so much time on this but I cannot get it return only those rows where "Sorter" is unique and rate is the lowest for that group (Sorter)The table looks like this:Sorter Lender Loan Rate Price APR101 1 $49,999.00 5.250% -0.450% 6.256%101 2 $49,999.00 5.500% -0.875% 6.344%101 3 $49,999.00 5.750% -0.750% 6.486%101 4 $49,999.00 5.875% -0.893% 6.536%102 2 $100,000.00 5.250% -0.560% 6.070%102 1 $100,000.00 5.500% -0.875% 6.169%102 3 $100,000.00 5.750% -0.750% 6.308%103 3 $125,000.00 5.250% -0.560% 6.036%103 2 $125,000.00 5.500% -0.875% 6.134%What I want to select and what I am looking for would look like this:Sorter Lender Loan Rate Price APR101 1 $49,999.00 5.250% -0.450% 6.256%102 2 $100,000.00 5.250% -0.560% 6.070%103 3 $125,000.00 5.250% -0.560% 6.036%Any help will be greatly appreciated.Bob
View 7 Replies View RelatedI would like to create a form with a double layered tab format that has the look and feel of the MS Access Options form under Tools.
What code or technique is required to make this work? For example, how do I make sure when the user selects the front row or back row of tabs that only this form is displayed.
I hope this is clear.
Dwight
Hi there,
I`ve got a problem with access. I Have data in several columns like:
Date Value 1 Value 2 Value 3 Value 4
1-1-01 12 10 11 9
2-1.01 14 7 16 11
etc.
Now I would like to add two columns that determine the min and max value of a row. Like below:
Date Value 1 Value 2 Value 3 Value 4 Min_value Max_value
1-1-01 12 10 11 9 9 12
2-1.01 14 7 16 11 7 16
Is this possible with the standaard min function? or how else do i do this?