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
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 RelatedOK, 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.
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.
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
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
I have a database of employees, sales, products(Sky and Mobile Phone Contracts) and customers(Need to be common and shared between all products). ( A door-to-door marketing company)
Since each product requires different inputting tables, I have inserted all the required fields into the tblSales and then I want to run a query in order to select the needed fields and create a new Sale of a specific product.
However at the same time I want to create a new customer, assign the sale to an employee, select the commission ID that he is going to receive for the job... etc. Well as you can see the problem is that it is not letting me do it due to the fact that an entry is missing in the other table for that client or a client is not yet created for the sale. (Only one autonumber is assigned.)
What am I doing wrong?
Just in case you want to look at my mess... I have attached the DB :P
Hello,
I have the following "range" table. I have a "txtBoxA" (numeric) and "txtBoxPrice". I have to look in the table for the corresponding range of "txtBoxA" and retrieve the VALUE for that range and store it in "txtBoxPrice"
LOW HIGH VALUE
1 500 40
501 1,000 70
1,001 2,500 90
Example: "txtBoxA" = 200 then the corresponding VALUE would be 40 and I would have to store 40 in "txtBoxPrice"
How would I do this??
I'm doing this in ACCESS
Thank you
My school coursework database is on my memory stick, and i have just opened it, and all my forms, reports and marcos have gone. The file has gone from 12000kb to 10000kb, and a table MSysCompactError automatically created itself. How can i get it all back?????
The last time i accessed the memory stick was on the school PC.
http://img485.imageshack.us/img485/1139/errorzm6.jpg
Hi everyone,
I'm developing a small Access 2003 application for a purchase service that includes managing informations about Suppliers, Customers, Carriers, and Orders.
With the Orders issue, I'v created a form to enter all the informations about the order; customer, supplier, carrier, orderID, and a subform to enter the items with their quantity and unit price... I want to:
1- Add a botton in the form that opens a Word document; a Purchase Order, that includes all the information entered in the form.
2- Add a botton to send this document as an attachement from Access via Outlook.
I need a guide step by step to understand how it works in Access with examples if is it possible.
Any help is highly appreciated,
Best Regards,
hello!
the scenario..... is i have a column, let say fieldx some of them have 10 characters that is 00000000a0 others are 8 characters 000000ao and i want the 8 char to be also 10 by adding additional 00? is this possible?
thanks...
hi
my problem is i need query code for
Concatenate Column Values from Multiple Rows into a Single row with Access
like this
Last First Code
------- --------- ----
Lesand Danny 1
Lesand Danny 2
Lesand Danny 3
Benedi Eric 7
Benedi Eric 14
Result should look like:
Last First Codes
------- --------- -----
Lesand Danny 1,2,3
Benedi Eric 7,14
hi everyone
urgent help needed with creating a query please...
I have a table with AutoNumber as primary key.
The second column is called ClassID and the third column is called TeacherID.
I want to run a select query that finds all records where the ClassID column and TeacherID column match.
Basically I want to find records where the fields are duplicated.
How do I do this either through Access query builder or SQL view?
Thanks in advance
Hi Friends,
I am not very gud in MS Access.Please check out the below example and help me.
Product_name Price
P1 $20
P2 $10
P3 $5.......
Mon Week_no Prod_name No_of items
1 1 P1 4
1 4 P1 10
2 4 P1 20
3 4 P1 10
2 3 P2 50........
Assume that Price of P1 changed to $40 in Feb.
Now I want to calculate amount earned by selling P1 from Jan to March.
Remember (For Jan the rate will be $20 and from Feb to March it will be $40)
If I calculate manually it will be - 4*$20 + 10*$20 + 20*$40 + 10*$40
But How do I do it using a query or code??
Hi guys,
im doing a project now, where i have to estimate the service due of cars. For this i need to put a count down timer in one of my FORMS and also REPORTS(sorry about posting about reports in this section but i ddnt want to post twice :rolleyes: but will do if necessary)... i searched a LOT on google but couldnt find anythng..... if some1 could come up with something reasonable, it would be VERY VERY helpful...
Please try to make it before this friday plz if you can...
thanks! :rolleyes:
Hi i have imported an excel sheet in form as an OLEUnbound.
The form is for registering product details and the excel sheet iam using for price calculations. But when i register a new product the sheet doesnt clear it shows the same values enterd as in the previous product and when i change them the same thing happens with the previous products. I want to use the same sheet with the calculation differently for all product.B`cus the products doesnt have same price.
Any help. I want to use that one sheet instead of making one own sheet for every products. Use the sheet as an mal or something like that.
Plz help.. :confused:
i have included a picture of my form.
when i should register a new product the sheet should be linked for that product only.
Hy, Ive got problem that drives me crazy. i have form with two combo boxes and text field which shows value from table based on a criteria from those combo boxes.
Can i use Dlookup function with more than one criteria like
Dlookup(myField,myTable,1valueOfTheField inTable= & forms!myForm!cboFirst.value &,2valueOfTheField inTable= & forms!myForm!cboSecond.value)
I also know how to make a querry in VBA that gives me correct value but i don't know how to put that value(which is currency) in textbox
Or is there other way to solve this problem and get correct value
Thanks for any help
i,m doing a project using Microsoft Front Page.It has to link to database.can i use Microsoft Access to do my database.if not,what should i use?
View 2 Replies View Relatedhello to all,
i need help with a basic problem. Is it possible to link a listbox and a calander,
My Main aim is to get a booking board for a hotel database, therefore i want it so that when you click on a particular date, the listbox displays all bookings on that date.
Also i need assistance with using queries for derived entities!!
Please help
Hi i have a problem coming up with the ans to the last part of this text. Can anyone help me?
The information technology (IT) department in your school has asked you, a part-time
employee at the department, to help create a database to track fundraising activities
and donations in your school. In the past, the schools fund-raising foundation has
been manually maintaining detailed records of donors and fund-raising activities. The
schools president has recently asked the IT department to help computerize the
foundations records. Your job is to design and implement the database for the
foundation. The following information is captured in the manual records:
1. Individual_Donor name, address, email and phone number.
2. Company_Donor company name, company address, company email address
company phone number, main contact, main contact phone number, main
contact email and phone number
3. Amount of donation and date of donation
4. The donation can be a one-off donation or a monthly donation (interval of one
month from the date of the first donation)
5. Name of fund-raising campaign (New building fund, President fund,
Scholarship fund, Bursary fund, Star fund)
1. Draw the ERD and database schemas for the donor database that you will
design for the foundation. Decompose the ERD where necessary. You can
make any assumptions that are reasonable for the design. Make sure to
suggest attributes that should be included but are currently not in the manual
system.
(5 marks)
2. Implement the ERD in Access. Design the tables for the donor database. If
the NRIC number of the individual donor is not available right now, what is an
appropriate primary key for the individual donor? Populate your database
donor with records.
...................(3 marks)
3. Create an input form(s) for the donor database. In your design of the input
form(s), create (a) an input mask for a control and (b) a validation rule for a
control
(4 marks)
4. Create a report that lists the donations in the order of descending amount of
donations. Put in the appropriate donation details. Separate individual
donations from company donations. Aggregate the donations for each
category
..(2 marks)
5. Perform the following queries on your database.
a. What is the largest amount of total donations received over a single
month period (first of the month till end of the month) for 2005?
b. Which are the company donors that donated on a monthly basis and
how much are the donations?
c. List the details of individual donors that donated to the New Building
and state how much they are donating to the fund and on what dates.
i want to execut two queries from unrelated table in single query.
is it posiible the below logic
"select PRODUCT_NAME from Products and
select Divisionname from Division "
i want to store colums of two different tables in single table
thanks in advance
narasim
i want to execute two queries from unrelated table in single query.
is it posiible the below logic
Query 1: select PRODUCT_NAME from Products
Query 2: select Divisionname from Division "
i want to execute these queries in single query so that
the resulting recordset contains colums of both the
queries
please help
thanks in advance
narasim