SUM Function In Query :: Newbie

Apr 5, 2006

Hi all,

Another quick Q - am a newbie at Access!

I am performing the following query:

.Open "SELECT DISTINCT([Date]), SUM([Batch Qty]) As [Batch] FROM [" & TableName & "] " & _
"WHERE [Date] BETWEEN #" & sDate & "# AND #" & eDate & "# " & _
"GROUP BY [Date], [Batch Qty]", cn, , , adCmdText


Data Produced:

29/03/200610
29/03/200620
29/03/2006100
30/03/200660
30/03/200680
30/03/20065
30/03/20065

How do I Sum the Totals for the Dates? So it produces:

29/03/2006130
30/03/2006150

TIA

View Replies


ADVERTISEMENT

Modules & VBA :: Creating A Function That Counts Records And Use That Function In A Query

Dec 11, 2013

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 Related

Query Newbie

Jul 26, 2005

I have been reading this is how I am able to articulate my problem with some degree of knowledge.

I have 3 tables as Follows:

Table 1. [ID]pk, [Contestant Name],[Event],[Category],[Class]

Table 2. [ID]pk, [Judge Name],[Event],[Category]

Table 3. [ID]pk, [Contestant Name],[Event],[Category],[Class],[Score1],[Score2],[Remarks]

Table 3 is a record of score from a specific Judge to a specific contestant. There will be a record for each contestant from each judge that is judging that specific [Event] & [Category]. therefore if there are two judges and two contestants there will 4 records entered into the table.

Problems 1. (I can't get my tables related properly)
I should not have to enter the duplicate information each time I enter a record into the Table3. The common information should be available within the other tables using queries.

Why i think I have problem 1:
There is a many to many relationship here on the parent form what i want is the [Judge Name], [Event], [Category].
On the related Sub form i want to see a query that shows me parts of tables 1,2,3 for each Contestant that is entered in the matching [Event],[Category] combined with the query of 1,2,3 and be able to enter this judges scores against the contestant Name and then when I change to a new judge I may see the same contestants but be able to enter a new score record against that contestant with a different judge.

In English
Assume you are a judge for a contest you have a form in front of you
Parent form Select your Event, Name, Category from a pull down box in the sub form you are presented with a list of Contestants that are on stage. You enter Score1 & Score2 for each Contestant and the record is created.

Judge #2 is doing the same thing
He selects his Event,Name, Category from the pull down menu on the Parent form and walla the subform populates with the same contestants as Judge#1 and he enters his Score1 & Score2 into the subform
and
What we have is 2 Judges, 5 Contestants and 10 Records created into Table3

Sorry for the length but the last part helps me visualize what I am trying to explain.

End result is I can't get it to work is this a job for a union query ?

View 2 Replies View Related

Add Query Help For A Newbie...

Jun 6, 2006

:( anyone pls help...

here's my setup i have 3 tables w/ same field(example Field name: PC)

i need to add all the values to another table because i need to get the quantity..

example Table1: 5 Table2:6 Table3: 7

Table4: 5+6+7 (18 should be in Table4 w/ field name Quantity)

can email me at icetumbler03@yahoo.com tnxx...

really appreciate and need asap.

View 1 Replies View Related

Newbie With Query

Aug 8, 2006

I have two tables

Table1 fields: Date, MoneyIn, MoneyOut, (plus other fields...)
Table2 fields: Date (some <> from Table1, other match), MoneyIn, MoneyOut, (plus other different fields...)

Question: Can I run a query that creates a list with
field1: all dates from Table1 AND Table2
field2: MoneyIn
field3: MoneyOut

Thanks

View 1 Replies View Related

Newbie Query

Jan 16, 2008

Hi,

I have two tables.

1. Customer (Id)
2. Transaction (Customer1Id, Customer2Id)

I want to delete all records in Transaction where Transaction.Customer1Id or Transaction.Customer2Id are not matched against the Customer.Id value.

I realise this query is very simple but I cannot get it to work in Access. Any help would be greatly appreciated!!

Thanks,
Ben.

View 1 Replies View Related

Newbie Needs Query Help

Oct 25, 2004

I work for a nonprofit. Everyone has 10 jobs. I am the only person who uses/has used access. I have a donor database listing names addresses, and different fields for donations made/not made for each year. I do simple queries to pull names from the list that our board members would like to personalize a letter to, and the rest goes to a mailing service for addressing and stuffing of envelopes. This year, we would like to send a different letter to those who have donated in any of the last 3 years. I can pull contact info and the 3 fields (donted 2003, 2002, and 2001) together in a query, but not sure what to make the criteria. If I put >1 (since some of my fields automatically have a zero, and are therefore not null), then I only get those names that have donated all three years. Some may have donated two years ago, and not again, some may have donated only last year, or 2 out of 3 years, etc. I know there must be a way to do this, but can't get my head there having only had basic interaction with access. I know by process of elimination how many there should be, so I will know if it runs correctly. Hope this makes some sense. Bear in mind, I am a novice, and certainly not a software person.

View 3 Replies View Related

Newbie Needs Help Writing Query

May 17, 2006

OK, I have two tables that have pretty much the same data in them, but, the first table has SOME data that the second table doesn't and I need to get that data into the table that does not have it.

Here's a description of what I want to do:

Table #1 has about 10,000 lines of data with the employee SSN as the ID for the records. In this table are two extra columns of data (HRContact)and(HR ContactCode) that are not always populated in Table #2.

Table #2 has about 300,000 lines of data with the SSN as the ID field. Some of the records that match the SSN's from Table #1 have the data HRContact and HRContactCode, but not all of the records have those fields populated.

So, what I need to happen is for the query to go through Table #1, find the SSN of a record. As it finds each SSN, it goes to Table #2, finds that same record with the same SSN, then looks in the HRContact field to see if there is data there, or if it is Null. If there is data in that field, then it goes on to the next SSN in Table #1 and repeats the preceeding process. If the data in HRContact is Null in Table #2, then it goes back to Table #1 and grabs the HRContact and HRContactCode data for that record and writes it into the HRContact and HRContactCode field for the record in Table #2. the query would repeat this process until it reaches the end of file in Table #1.

I hope this is clear and if you have any questions, please ask me...

Thanks for your help,
Dave

View 1 Replies View Related

Newbie Query Problems

Jul 28, 2006

I have an asset table, an equipment table, and a customer table. The asset and customer table has a field DateOut and equipment table has a field OnHand. I would like the OnHand table to decrement by one each time the DateOut is set to current, Date(). Tried Iif, tried expression builder, now completely lost. Any help would be great.

View 1 Replies View Related

Newbie Help With A Complicated Query

Sep 26, 2006

First off let me say that I'm VERY new to Access. I've been put in charge of administering a large (to me) database and I've just started working with the insides of the database for about 3 weeks now.

The most urgent thing though is I need to make an "Honour Roll" report for the employees, that is, they get pass/fail inspections and I'm trying to create a query that returns all employees that have 10 or more passes IN A ROW, with at least 2 of those passes being a personal evaluation vs. an after the fact quality verification. There's already a query in the database that returns the employee ID, total passes, total fails. I've managed to add the field that includes type of inspection, but I can't seem to find a way to count the number of PE's in the field.

The second issue is getting the query to ignore all inspections prior to their last fail. I think I can work out moving the date of the inspection into the query, but then using it is another story. If it requires a script of some sort, then I'm hopelessly lost. The last programming I did was BASIC back in school... many years ago.

If I were to break it down into a statement, it would be something like this:

If (employee passes) >= 10 since last (employee fail) AND (inspection type="PE") >=2, then send (employee ID) and info to a report.

Any help with this is appreciated, I'm starting to get desperate!

View 2 Replies View Related

Query Code In VBA - Newbie

Oct 5, 2006

In the code of a button, ive got....

Which should be pulling the value in cb1 in the last cat query, but im getting an error saying it cant find the field "|" refered too...

Basically there is a table called Last Cat, and I want to draw the value of cb1 out of it where the catpathid = 5 so i made the query to do that... but now im abit lost....

Me.combo1.Value = [Last Cat Query1]![cb1]

any help would be awesome.

View 1 Replies View Related

NEWBIE: How Do I Convert A Query To Text?

Mar 15, 2006

Hi,

I have a database with a query that returns a list of printers. This is created by finding all the printers in the database that take a perticular cartridge by code. Sample below.

PrinterName
-----------------
Some Printer 1
Some Printer 2
Some Printer 3
ETC

What I want to do is take the results from the query and convert it into a single line of text and place it into a column of a table related to that cartridge code.

So if say the cartridge code '12345' returns a list of printers such as;

printer 1
printer 2
printer 3

I want to take this data and turn it into a line like this;

printer 1, printer 2, printer 3

I then want to take this new line and place it in a column called Desc in another table relative to the original cartridge code.

table should look like this after query.
BEFORE:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 |


AFTER:
CartCode | CartDesc | Price | Desc
12345 | Something here | 10.00 | printer 1, printer 2, printer 3

I hope what I have said makes sence. I am pretty new to access and feel I have done quite well so far. Please can someone help me solve this problem?

Thank you

View 4 Replies View Related

Newbie Post - Query Question

Mar 14, 2006

Hi, i'm a newbie so please be gentle :)

ok, i have 2 tables, one is for part number and product ID and the other has product ID and Vehicle details.

i know how to set up a parameter query but just can't quite get the result i want, which is to set up a query based on the result of the first query.

in short i want to enter a part number and it comes up with the vehicle details.

Many thanks in advance

Ty

View 5 Replies View Related

Newbie Dont Known Where To Start My Query

May 28, 2006

Hello, Really hope someone can help me.
I have 2 lots of info in 1 table - nameley customer address & posting address

I need to print a form that always has the posting address on the same side. but in some cases if the postal address is the same as the customer address then it is not filled in. hope this makes sense:

Please Im Really stuck

View 2 Replies View Related

From Form To Query - Access Newbie

Aug 24, 2005

Hi all. I am fairly new to access and am trying to do something that shouldn't be that hard. I have a simple form with one text box and a command button on it. I have the command button set to run an update query.

My problem is that when I enter data and press the button, I don't know how to get the entered data into the query?

I know this is simple to do, but since I am new to access, I can't figure it out. Does anyone have a simple example or step by step details that they can share with me?

Thanks

Chris

View 2 Replies View Related

Newbie Question - Adding A Field To A Query

Sep 22, 2006

Hi there - i'm looking for a bit of help on something that is probably obvious to experienced Access users - i've checked the forum for about 25 min or so and couldn't find a related answer to this question.

I have a select query (QRY2) that reads another select qery (QRY1), and I need to (or rather, i'd like to) add a field to QRY1 - how do I get it so i can view this new field from QRY1 in QRY2?

thanks so much for anyone who could help me!

View 4 Replies View Related

Access 97 - Query Help - Newbie - Removing Duplicates

Jan 25, 2007

Hi,

just finding my way in Access 97 with no real support. I have made a query that creates duplicate records when I only need to see one for a certain criteria (lets say criteria X). This is due to the data feed involved in making the query where criteria X is found in numerous records.

What I want to display is only one record per entry containing an exact match for criteria X.

Finding it hard to articulate this but I would need something that defines:

If criteria X is the same, just display one record.

Hope this is somehow clear as I am struggling a bit. Would be gratefull of some help.

Thanks

View 3 Replies View Related

Newbie Confused With (probably) A Simple Query Issue

Oct 23, 2004

Hi, I've looked all over the forum for an answer to my problem and can't find it. This will probably make regular contributors groan, but I'm totally confused and if someone could help that would be ace.

I've got a popular budget airline website (never mind it's URL, I don't like Spam either) and want/need to move it to Access and thence to run user queries via SQL.

So far so good.

So I set up an access DB:-

I've got three tables:-

Airline Info has three fields:- Airline Code (3 letters, primary key), Airline Name and Airline URL.
Airline Routes has four fields:- RouteID (9 letters, primary key), Airline Code (3 Letters), From Airport Code (3 Letters), To Airport Code (3 Letters).
Airport Info has three fields:- Airport Code (3 letters, primary key), Airport URL and Airport Name.

Hopefully anyone reading is still with me.

I've set up the relationships so that Airline Info.Airline Code is linked to Airline Routes.Airline Code.

Now I want Airline Routes.From Airport Code and Airline Routes.To Airport Code to both link to Airport Info.Airport Code (the ultimate idea being to find the Airport Name), and Access will let me do that but therein the problem lies.

Essentially I want a report that would say (eg) Jet Blue fly from John F Kennedy New York to Los Angeles.

All I can manage is to get "Jet Blue from John F Kennedy New York to LAX". I can convert the From airport code to its name, but not simultaneously convert the To airport code to its name. It's gotta be something to do with the query, but I don't know what and have spent two days on this now Anyone a) understand all of that b) know what's going on?

Ta in advance,

Alex.

View 7 Replies View Related

Use A Query For A Query, Newbie Question

Apr 23, 2007

Newbie question: In datasheet view, I don't see an option to save the data as a table (so I can use the table for other operations).

For example, if I create a query in design view, the "Save" option only seems to save the query-syntax, not the actual data. So I end up having to first export the data to Excel and then import it as a table. Seems kind of silly the idea of having to rely on a spreadsheet to build a database table. Can't I use the database to build a databae table?

On a related note, I had hoped to use one query as a "database object" accessible from a second query. What I mean is, having saved Query1, I wanted to write: SELECT FROM Query1 Where... but I got an error when I tried this.

View 2 Replies View Related

Queries :: Update A Query Based On Results From Another Query Using Count Function

Apr 2, 2013

I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).

I have 3 queries which count how many patients come in 5, 4 and 3 times/week.

In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".

I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.

(I'm not using SQL view, I'm using the query design view)

In the "update to:" row, I use the Build function and locate the count I'm looking for.

Problem: when I run the query I get the error: Operation must use an updateable query.

View 3 Replies View Related

Function Query

Mar 5, 2007

Please help, the attached code works fine in Windows XP but twhen run on a Windows 2000 pc it fails on the create object, any ideas?

Function GetXmlFilename()
Dim objDialog
GetXmlFilename = ""
Set objDialog = CreateObject("UserAccounts.CommonDialog")
objDialog.Filter = "XML Files|*.xml|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C: emp"
If Not objDialog.ShowOpen Then
Exit Function
End If
GetXmlFilename = objDialog.Filename
End Function

Thanks in advance

View 2 Replies View Related

Use A Function In A Query

Jan 17, 2006

I have written a simple function that I would like to use in a query. The function would return a value for each record where the parameters equals 2 different fields of the same record.

Private Function FctConvertInch(LineDim As String, LineShape As String)

'To Convert a String to a value and then convert that value from mm to inches
'Lines dimension = 00.0000x00.0000 if line is flat
'Lines dimension = 00.0000 if line is round
Dim StNum1 As String
Dim StNum2 As String
Dim DbNum1 As Double
Dim DbNum2 As Double

If LineShape = "round" Then
'"CDbl" convert a string to Double number
FctConvertInch = CDbl([LineDim]) / 25.4
ElseIf LineShape = "flat" Then
StNum1 = Left([LineDim], 7)
StNum2 = Right([LineDim], 7)
DbNum1 = CDbl(StNum1)
DbNum2 = CDbl(StNum2)
FctConvertInch = (DbNum1 / 25.4) & "x" & (DbNum2 / 25.4)
End If
End Function

I have put the function in a new module, and have try to use the function in a query with the fields LineDim and LineShape , I've also added the field FctConvertInch(LineDim,LineShape) but it doesn't work. Am I using the function the the correct way?

View 10 Replies View Related

Query With LEN Function

Jul 25, 2006

i want to write a query where i ask it to update a table where the text is

like "Ack. Stamp"

And the lenght is less than 15

how do i do this

View 1 Replies View Related

SQl Query Or Function

Dec 14, 2006

Hi to all,

I have a table consisting of 12 columns of data type number. for each row i want to display a count wherever the value of a row is greater than 100, that is, how many times the value greater than 100 appear in the 12 columns.
PLease help???

Thanks

View 1 Replies View Related

Using IIF Function In Query?

Jul 5, 2007

I have a field in a query that I want to select the value from a field, but if the value is null, then to select the value from the same named field in a different table.

Basically the whole point is to use the given description unless one doesnt exist, where the default one will be inserted instead.

Cheers,

View 5 Replies View Related

Using A Function In A Query For If/then/else

Jul 12, 2007

I have a field I want to calculate using IIF. There are about 40 possible values, thus I have about 40 nested IIFs. Because I get "query is too complex" when I try to nest all 40 IIFs, I've had to break this down into 3 fields. I then have to do a little more manipulation to end up with the 1 field I originally wanted. All that works fine, however, I'd like to be able to calculate the value in 1 field instead of 4 fields.

I'm not too good with VBA, but looking at some examples, I tried creating the following function (this is just a small sample, but if I'm on the right track there will be about 40 of these "Step" variables and ElseIfs):

~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Function IfThen(BYStep As String)

Dim test2 As Integer
Dim Step070 As Integer
Dim Step075 As Integer
Dim Step080 As Integer


If
BYStep = "07.0" Then
test2 = Step070

ElseIf BYStep = "07.5" Then
test2 = Step075

ElseIf BYStep = "08.0" Then
test2 = Step080

Else: test2 = 0
End If

End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In my query I have:
IfThen([BYStep]) AS Test2


It took me awhile to get to the above where when I ran the query I didn't get a message about the wrong arguments. Now, I don't get an error message, but the field Test2, comes up empty for every record.

Is there a way to do what I'd like to do (having the 40 if/then/elses in a function, and then using that function in 1 field in the query)?

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved