Sum() In Empty Table Doesn't Return Zero

Jun 12, 2007

i have a query with this basic structure

SELECT id FROM table1,(SELECT SUM(field2) as total2 FROM table2) - (SELECT SUM(field3) as total3 FROM table3) as total

to understand better let's say that:
table1 are clients
table2 is money that enters for clients
table3 is money that exits from clients

i want to obtain a balance. the problem is that if table2 or table3 has no records for certain client, this client is exluded from results

any ideas?

i tried using ISNULL like this:

SELECT id FROM table1,ISNULL((SELECT SUM(field2) as total2 FROM table2),0) - ISNULL((SELECT SUM(field3) as total3 FROM table3),0) as total

but it didn't worked
this is the query
SELECT id, UPPER(apellido_titular) + ', ' + nombre_titular AS padre,
(SELECT SUM(facturas_items.importe) AS totalf
FROM facturas INNER JOIN
padres ON facturas.id_padre = padres.id LEFT OUTER JOIN
facturas_items ON facturas.id = facturas_items.id_documento
WHERE (padres.id = p.id) AND (facturas.fecha_vencimiento < GETDATE())) -
(SELECT SUM(recibos_items.importe) AS totalr
FROM padres INNER JOIN
recibos ON padres.id = recibos.id_padre LEFT OUTER JOIN
recibos_items ON recibos.id = recibos_items.id_recibo
WHERE (padres.id = p.id)) AS total
FROM padres p
WHERE (activo = 1) AND
((SELECT SUM(facturas_items.importe) AS totalf
FROM facturas INNER JOIN
padres ON facturas.id_padre = padres.id LEFT OUTER JOIN
facturas_items ON facturas.id = facturas_items.id_documento
WHERE (padres.id = p.id) AND (facturas.fecha_vencimiento < GETDATE())) -
(SELECT SUM(recibos_items.importe) AS totalr
FROM padres INNER JOIN
recibos ON padres.id = recibos.id_padre LEFT OUTER JOIN
recibos_items ON recibos.id = recibos_items.id_recibo
WHERE (padres.id = p.id)) > 0)
ORDER BY UPPER(apellido_titular) + ', ' + nombre_titular

sorry for my poor english

View Replies


ADVERTISEMENT

Ctrl+F Doesn't Return No Find

Apr 25, 2005

I've been using the ctrl+F search in order to search my db for a string or phrase (from a form). Now, after I put it on the Intranet and accesss it through there, I use ctrl+F and if I search for something that is not there, it doesn't return a "did not find" box. It's like it just keeps searching.

Is using the find feature like this just bad form and I should write queries for users to search the db?

Comments/critiques/criticisms welcome. Thanks.

View 1 Replies View Related

Query Doesn't Return Any Fields

Mar 7, 2007

I created a database that contains 2 tables. I then created a query that simply groups the data and calculates a row total. And it works as expected. However, when I try to create a report, using the query, I get, "The wizard was unable to generate fields from the record source you chose. Perhaps you chose a query that doesn't return any fields."

What am I doing wrong?

Screencaps attached.

Thanks.

View 9 Replies View Related

Return To Empty Field Before Closing

Aug 7, 2015

I have a form that requires a specific combo box selection completed prior to closing or it doesn't register the field ID# in the table or display the payment in the payment query. When I click on the accept button on the form, if [PaymentMethodID] is blank, I want to put up a msg box informing me to "choose a Payment Method", then return the cursor to [PaymentMethodID] field to allow the user to make the appropriate selection.How would I write that in VBA.

View 8 Replies View Related

Modules & VBA :: DLookup Doesn't Return Correct Value

Aug 3, 2015

I'm trying to use a DLookup to get a specific value from a field in a table.

This is what my code currently looks like;

Code:
JOBID = DLookup("[ID]", "MASTER PLANNER", "[JOB NUMBER] = '" & JOB_NUMBER & "'")

Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.

However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.

what I'm doing wrong?

View 9 Replies View Related

Queries :: Return Records With Both Empty And Populated Field?

Apr 8, 2013

I am trying to create a query that returns records whether a field has data or not...

There are three fields in question, SSN, DOB (this is a date field), POB (this is a foreign key representing a state in the query shows the actual state). Now unless the criterion is different then I just need the answer for one I can reproduce.

I would like to do this in the criteria box in the query.

The query pulls from one table, some of the employees in this table have the three fields populated some don't. I would like the query to return all employees...

View 2 Replies View Related

Queries :: Return Word Unknown For Any Code That Doesn't Have A Match

Aug 20, 2013

I have lookup table I use to return names for various "Sales Class" codes.It all works good but if there is a code that isn't in the lookup table it leaves that field empty.I want it to return the word "Unknown" for any code that doesn't have a match.Here is the SQL:

Code:

SELECT [Data1].OrderNum, SalesClasses.[Name]
FROM [Data1]
LEFT JOIN SalesClasses ON [Data1].[Sales Class] = SalesClasses.[Code1];

Table examples:

Data1:
OrderNum - Sales Class
111 - class1
222 - class2
333 - classX

[code]...

View 3 Replies View Related

Problem With Null Or Empty Table

Dec 12, 2005

I have the following problem:

I have a make table query that sum a total amount in order to have only the total in the table field.

However, when the total is 0.00, the make table query says "you are about to pos 0 rows" instead of postin 1 row with 0.00.

How can I set the query in order to have a 0 in the table when the total is 0 instead of having nothing posted ?

I have tried the is null, is empty, is missing, nz and nothing seems to work.

View 1 Replies View Related

Closing Form With Empty Table

Oct 6, 2004

Can anyone guide me to how to code a form that will not error when closed if nothing has been input to the first field which has the focus? I have a start up form that has fields connected to a table and if this database is open and the user desides not to use it then closes it without entering any data it will error. Is this a recordset issue? Thanks

View 1 Replies View Related

Modules & VBA :: Dmax For Empty Table?

Apr 15, 2015

How do i use DMax() function instead of Autonumber, since the table is empty and it wont know what to do with Dmax() + 1 for next record?

E.g I have a new table Customer_details which contains

customerID
customerName
Age

now if i enter data through forms, i want the CustomerID to be incremental for each new record, so i can use Dmax() function. But since the table is empty how can i tell the form, through VBA that if it is null store 1 else store Dmax("CustomerID", "Customer_Details") + 1

View 2 Replies View Related

Data Is In Me![fieldname] But Doesn't Get Stored In Table

Apr 22, 2006

Hello all :)

I've read some really funny stuff while searching for an answer to this. You're a bunch of really witty types :)

So I'm sure someone can help me - why would this entered data not make it into the table?

I have a form that has a subform. After the subform I have a button to continue. If this is clicked I change the visible property for another field to true. This field appears to work fine, but the data doesn't make it to the underlying table, in fact it sometimes seems to be included in the next record.

Any ideas?

Thanks
A.

View 4 Replies View Related

Table Validation Rule Doesn't Work...

Jan 28, 2005

Hello, all.

I have a table that contains a field (list) for categories [Inquiry Type], and a field that tracks category of any follow-ups [FU Inquiry Type].

The rule is: If [Inquiry Type]="F", than [FU Inquiry Type] Is not null.

For some reason, when I'm entering data, the validation rule does not take any effect....does not notice when [FU Inquiry Type] - which is also a list box - remains empty when [Inquiry Type] is an F.

I have entered the rule this way in the table property validation rule field:

(([Inquiry Type]="F" AND [FU Inquiry Type] Is Not Null) OR ([FU Inquiry Type] Is Null)

I already have data in this table, but have ensured that it conforms to the above rule. Can anyone help?

View 4 Replies View Related

Field 'F1' Doesn't Exist In Destination Table 'tablename.'...

Nov 20, 2006

"Field 'F1' doesn't exist in destination table 'tablename.'"

I hate this error message.

I am using the following command to load data from an excel spreadsheet into a backend SQL Server database via an .adp:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, sTempTable, strFileName, False, "A2:B4000"

I have purposely used "False" to ensure that the first line in my spreadsheet is ignored. This is because the first line in my spreadsheet contains headings that do not match the column names in my table.

I do not wish to change my headings as end users will be making use of my application and they will not like headings such as "int_FactoryID". Likewise I do not want to change the column names in my table to words such as "Factory ID" as this would be a bad naming convention.

Is there a way to use TransferSpreadsheet without necessarily matching the headings in the spreadsheet to the column headings?

Is there a way for TransferSpreadsheet to ignore the headings and assume that the first column in the spreadsheet needs to go to the first column in my SQL Server table?

Any help would be appreciated.

Thanks
Kabir

View 1 Replies View Related

Customer ID Doesn't Show Up Correctly In A Query Or Table

Oct 11, 2004

When I open one of my tables or queries and look at the customer Id, the displayed info is a single digit. On my customers table my primary key is the customer Id with auto number with the format like this: "ID"000. This is great however, when you look up the customer id in other tables it only displayes the single digit. I want it to show up like ID001 or ID002. In the customers tables it looks just like that, but if you click in that cell/field it takes away the ID and just shows the 001 or 002. I changed the format in my other tables under customer id to "ID000" but that didn't seem to make a difference. I would like to be able to do a search lets say by customer ID in one of my products table and when I type ID005 or ID012 it takes me to those records. But right now I would have to enter 5 or 12. Datatype in my other tables on field customer ID is set to text. Do I have to do a validation rule? How can I get it to show up the way I want it?

View 7 Replies View Related

General :: Scroll Wheel Doesn't Work In Only One Table

Jan 11, 2013

happen- scroll wheel stops working in one table- the rest you can scroll in as usual? I know there can be issues with scrolling when you're missing some driver for your mouse, but this happened all of a sudden to just this one table, so I can't imagine that it's the mouse missing any drivers. I did compact/repair, restarted, and also tried repair of office installation. That table still won't allow scrolling unless you move the scroll bar manually first.

View 3 Replies View Related

Modules & VBA :: Doesn't Find Existing Symbol In Table

Nov 1, 2013

I am tying to query the Symbol table to see if a recod exists with symbol code.

I am querying the tblSymbol table from Excelk vba and the Access DB is on my machine.

The code I am using is:

Set rs = New ADODB.Recordset
rs.Open "tblSymbol", cn, adOpenKeyset, adLockOptimistic, adCmdTable
Set rs2 = New ADODB.Recordset
sql = "SELECT * FROM [tblSymbol] WHERE [SymbolCode] = """ & someSymbol & """"
rs2.Open sql, cn, adOpenDynamic, adLockOptimistic

[Code] ....

MsgBox Exists always returns -1 but i know the code exists in the table.

View 5 Replies View Related

Access Doesn't Allow Change Primary Key In A Linked Table

Sep 28, 2015

Currently we have a web based order taking/inventory system. Every night they export a .db to an FTP server and every morning I download it (I automated this part, it replaces the older file). I link to this database (using SQLlite3 to ODBC driver) from an Access database for specifically this purpose.

When I originally created it I had to go through every table and create a unique identifier. Unfortunately I chose poorly in some cases. I can't figure out how to get those primary keys changed to the correct ones. I've tried recreating the ODBC connection, tried a new database and new ODBC connection, tried killing the connection waiting a day (for the new .db to download) and recreating the connection and trying it in a new database.I know access doesn't allow you the change the primary key in a linked table.

View 14 Replies View Related

Insert Empty Record Between Two Records In Unordered Table

Aug 1, 2005

Is anybody can help me to solve problem with inserting empty record between two records in unordered (non-indexed) table - like in Excel work sheet.

Regards,
Gennady

View 4 Replies View Related

Reports :: Visibility Based On Whether Table Field Is Empty

Jul 11, 2013

I'm having trouble with syntax for using VBA on a report in Access 2007. I need to some hide 3 text boxes if one of my fields is empty in a table. The table name is: 'ALM-RESP' with rows grouped by: 'TAG NAME' and the field I'm checking is: 'Rev 002 Author'. I've tried having a rectangle to cover it up but I'm having trouble using the IsNull command and setting the visibility of the rectangle. Here's my code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull([ALRM-RESP]![TAG NAME]![Rev 002 Author].Value) Then REV2BOX.Visible = False
End Sub

Basically I'm having trouble checking if the field is empty and then setting the visibility.

View 1 Replies View Related

Queries :: Update Table Where Column Cell Is Empty?

Aug 6, 2015

I have a small problem: I have a table with over 20 columns. In every column there are some 1's, but the other cells or tuples are empty. In these empty cells I want to write a 0. So I need an UPDATE query.

But what should I write for the WHERE condition? So he should check every cell and if there's no 1, write a 0.

View 7 Replies View Related

Tables :: How To Insert Text Box Value To First Empty Cell In Table

Mar 17, 2014

I have a simple form (frmAddPaper): txtPaper where user enters name of new newspaper, and cboCity, where user selects the newspapers town.

Also Close and Save buttons.I also have a table, tblCity. Columns as follow: CityID, City, Paper1, Paper2, Paper3, Paper4, Paper5, Paper 6.

Some cities have values (Newspaper names) in just Paper1 field. Others in Paper 1 & Paper 2, and some in Paper1, Paper2 and Paper3.I want to add the txtPaper value, to the first empty column, in the row where cboCity matches City column.

View 14 Replies View Related

Linked Table Manager Doesn't Relink ODBC Tables.

Sep 15, 2006

Hello,

The scenario. Two PC's, one older than the other, both running Windows 2000 (SP4) and Office 2000 (SP3). I use Access as a front end to a MySQL database, connections are made using ODBC.
I recently altered the structure of a table and attempted to re-link the table in access using the Linked Table Manager on the new PC. All that happened was the hourglass came on and never went away. I tried to do this on the old PC and it worked fine first time.

Any ideas why I can't get it to work on my new PC? It's quite important as I won't have the old PC with me much longer :-(

Regards,

Matt.

View 3 Replies View Related

Queries :: New Record Added To Table But Doesn't Show Up In Query

Aug 4, 2014

I have a database used to track my personal assignments, created about six years ago using Access 2003 on Windows XP. Recently upgraded to Access 2010 on Windows 7. At some point thereafter, I started having the following issue:

When a new record is created, that record gets added to the table, but doesn't show-up in any query, form, or report until after another new record has been added. The most recently added record cannot be located to view or update, except in the table, until after another new record has been added to the table. Queries, forms, & reports now always lag behind by one record.

None of the queries, forms, or reports tested contain filters. I have several multi-user databases that I also support and none of those users have reported having this problem. This is only happening on my personal database.

I've re-created this database once or twice in the past to resolve other issues, but would like to avoid that route this time around, if possible.

View 7 Replies View Related

Tables :: Updating Specific Records In A Table Using Sql Doesn't Work

Sep 15, 2014

I Have a table that contains the fields: CAT, CHAPTER, ID, someSrting and Completed

CAT, CHAPTER, ID are numbers and Completed is true/false. If I mark a certain entry as Complted (true), I want all the records with the same (CAT, CHAPTER, ID) as the one I marked to be updated to Completed.

For example, If I marked the entry cat:1 chapter:1 id:1 as completed (true), I want all the entrys that have cat:1 chapter:1 id:1 to be marked as completed (true)

the code I wrote is:

Code:
Private Pub Completed_check_AfterUpdate()
Dim myCC
Dim myCat
Dim myChap
Dim myID
myCC = Me.Completed_check
myCat = Me.CAT
myChap = Me.CHAPTER
myID = Me.ID
CurrentDb.Execute "Update [my Table] " _
& "SET Completed = (" & myCC & ") " _
& "WHERE CAT = (" & myCat & ") And CHAPTER = (" & myChap & ") And ID = (" & myID & ") ;"
End Sub

I'm running the code (my clicking the "check box") and nothing happens, I was thinking that maybe I defined the after "WHERE" statement wrong, and there are 0 entrys changed..

UPDATE: after removing the

Code:

"& "WHERE..."

row, the code does update the entire table, so I have a problem with the syntax of the sql..

View 1 Replies View Related

Forms :: Table Doesn't Read Combo Box Info Correctly

Nov 24, 2014

I have a form "FrmMaintenance" based on the query "QryMaintenance" that is taken information from the table "TblMaintenance".

In the form, I have to pull the serial number of a machine from a drop down list which works perfectly fine in the form and it also updates other associated records.

When I go to the table, it puts associated number instead of the serial number that I've pulled.

Same thing happening for the pull down list "Engineer".

I've tried to look at the format of the "SerialNo" and changed to text, then to Combo Box but it didn't work, and the same for "Engineer"

View 8 Replies View Related

Queries :: Field In Table Doesn't Change When Edited From Query

Mar 29, 2013

Users are viewing a record on a form that gets it's data from a query. I want to make it so that if they edit that record from that form, a last updated field will timestamp the date/time that the record was edited. I added a lastupdated to the source table which of course adds it to the query and so it's on the form.

But whenever i access the form and change something, the lastupdated only shows the time the record was created (which is from a different form based directly on the table). Whenever I edit any other field data in that query based form it changes in the table. Why not the Lastupdated?

View 2 Replies View Related







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