Joins Producing Unexpected Number Of Records

Apr 17, 2008

i have a table with 3740 records in it

i do a query and outer join this table with a query and it produces LESS records = 3733

:confused:

now i always assumed (never assume it makes an "ass" out "u" and "me" !) that the records would be the same as the table you were joining from, obviously i've assumed wrongly, but why?

the query i'm using as my lookup query is grouped and i think that is causing me problems...if i turn it into a table, it works as expected

:confused:

and i check the obvious: no filters or SELECT DISTINCT...

any suggestions/explanations welcome

View Replies


ADVERTISEMENT

Converting Text To Number Producing Error Data

Nov 5, 2005

I am using the funtion:

Clng([text field]) to convert a text field into a number so that I can link in a query. Because it is an informix database, I cannot change the underlying table format.

When I run the query, the data is showing "Error".

I tried changing the other table to text and linking but it doesn't seem to link properly because the query doesn't work.

Your advice appreciated.

View 5 Replies View Related

Query Not Producing All Records

Feb 19, 2008

I have done some searches and found out about creating joins and have almost achieved the results I want. I have 4 tables (Invoices, Labor, Purchases, and Payments). All are linked by Invoice number and all of the data is enter via a form called invoices and has a subform for all of the other parts. I am creating a query to pull the total amount of the invoice from the parts of the table and then taking the payment and showing a report for this.

I created 4 total queries and then have nested them into a query. I then perform the calculations and have my results that I can then put into a report. Only problem is that if there is not data in the labor table for an invoice it does not show the invoice in the final query. Homever, if there is no payment data or purchase data it will show the invoice and leave those fields empty. Not sure why this is happening I used the same type of join on all of the queries.

Would love to try and understand why this is not working correctly. I can get around this by just entering a 0 for work hours on the subform, but I am thinking that there should be a way to this without doing that.

Anyway, if any one can help me understand the problem, it would be greatly appreciated.

I have attached a very scaled down version of the tables I am working with and the problem is in the InvoiceTotalsQuery. I have not performed the calculations in this, just want to see why invoice 7 is not showing.

Any help is greatly appreciated.

View 4 Replies View Related

Query Producing Too Many Records

Feb 3, 2014

My raw data contains a text string with various codes separated by an underscore. For example, my raw data would contain a string like this, "SAMPLE_DATA_ABC1_NYC", and each of those codes would have corresponding name that is mapped out on another table.

I'd like to set up a query that would evaluate the raw data, look up the associated code (e.g. "_ABC1_") and then return the corresponding name. (See screenshot for an example.)

I tried using IIf and InStr statements, but I keep getting excess rows.

Code:
SELECT st.Campaign, st.Month, st.Week, st.Placement, st.[Placement ID],
IIf(InStr(st.placement, ic.[findInventoryCategory]) > 0,ic.[InventoryCat],"N/A") AS [Inventory Category],
IIf(InStr(st.placement, pth.[findPathway]) > 0,pth.[Pathways],"N/A") AS [Pathway Name]
FROM RawDataFile AS st, mpInventoryCategory AS ic, mpPathway AS pth;

View 8 Replies View Related

Query Not Producing New Records Added To A Table?

Nov 28, 2007

Hello,
I have created a form to enable data to be easily added to the database table, however does anybody know why the query that looks up the records from the table does not return the new entered records, only what was originally in the table?
Thanks

View 11 Replies View Related

Number Of Joins

Dec 5, 2005

Is there a limit to the number of joins allowed in a query?

View 2 Replies View Related

Using Two Outer Joins To Get A Record Count With Missing Records

Feb 5, 2008

Hello All,

I have three tables: Employees, Gender, Diversity. Both the Gender and Diversity tables are one to many relationships with the Employees Table. I am trying to run a query that will output a count of all diversities and genders. For example:


Diversity-----Gender-----Employee Count
White----------Male---------------1
White----------Female------------5

Asian----------Male---------------3
Asian----------Female----------<Blank>

Hispanic-------Male-------------<Blank>
Hispanic-------Female----------<Blank>


I am trying to get all counts, even if the combination of diversity/gender is not in the employees table. I am going to use that information in a Crosstab query.

What I thought would work was do a Left Join For Diversity and Employee such as:

SELECT Diversity.[Diversity Description], Count(Employee.ID) AS CountOfID FROM Diversity LEFT JOIN Employee ON Diversity.ID = Employee.Diversity GROUP BY Diversity.[Diversity Description];


Then, do a Left Join for Gender and Employee such as:

SELECT Gender.[Gender Description], Count(Employee.ID) AS CountOfID
FROM Gender LEFT JOIN Employee ON Gender.[Gender ID] = Employee.Gender GROUP BY Gender.[Gender Description];

And then do a Union. But that doesn't work.

Any thoughts or comments would be much appreciated!

Thanks,

Jon

View 1 Replies View Related

Help Please With Multiple Outer Joins To Return All Records From Main Table

Jun 5, 2007

I have this query in design view and in an asp page and it works fine:

SELECT dbo_feedback.*, dbo_origin.originName, dbo_product.prodname, dbo_category.catName FROM dbo_product INNER JOIN (dbo_origin INNER JOIN ((dbo_feedback INNER JOIN (dbo_category INNER JOIN links_cat ON dbo_category.catID = links_cat.CatID) ON dbo_feedback.id = links_cat.FeedbackID) INNER JOIN links_product ON dbo_feedback.id = links_product.FeedbackID) ON dbo_origin.originID = dbo_feedback.origin) ON dbo_product.prodID = links_product.ProductID;

BUT, I want to return all feedback entries, even if they have no matching Product or Category. :confused: When I change the inner joins to outer joins I get a syntax error in the browser window. I changed the join type in the relationship diagram in Access and tried to recreate this in query designer, but Access says the statement cannot be executed because of ambiguous outer joins.

FYI, one feedback can have many products, many categories, and only one origin. I have the joins correct and enforced.

Please help, thanks!!!

View 1 Replies View Related

Query Using Joins To Disply All Records From Both Sides Of Join Type

Apr 14, 2008

I have a list of PC SN#s in tbl1;
I have a list of other PC SN#s in tbl2
I want to display ALL records of tbl1, AND ALL records of tbl2, matching up the SN#s (where there is a match) - but I want to display all records whether they match or not.

How can I accomplish this?

View 1 Replies View Related

'JOINS' Need Advice On Joins

Aug 14, 2007

Hi again,

I just restructured my DB and I was wondering if anyone can give me some advice on whether or not my joins/relations are correct. I left some joins/relations out because I wasn't sure what relation I should use.

Any Advice will be greatly appreciated

18418

View 2 Replies View Related

Unexpected State

Jul 13, 2005

Ok, I know you guys have heard about this problem quiet a few times, my search told me that. But my problem has a bit of a twist. The message appeared when I tried to open the database yesterday, so I went to the backups and tried to open them. All the backups for the past month give the same message. The problem is with the FE only, the BE is working fine. The backups are connected to the same workgroup as the FE and BE. Has anyone seen this before and know what might of happened?

View 8 Replies View Related

Forms :: Auto Generate File Number Based On Number Of Records In Year

Jan 21, 2014

I have a form [IUDATA]

I have a add record button.

I have a date field [DATEIN]

I have a text field [DRPNO]

If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.

I'd like the format of [DPRNO] to be "dpr YY-XXX"

Where:
YY is the year of the [DATEIN] field and
XXX is number of records in that year.

So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.

View 12 Replies View Related

Unexpected Error (40230)

Dec 1, 2005

Ever since I switched from using a Mapped Drive to using the UNC path for my linked tables, i occassionally get Unexpected Error (40230).

I noticed this first when i manually re-linked using the link table manager, it linked the tables, then gave me this error several times, likely reflecting the number of tables i linked, but the links refreshed.

I then noticed this error when i used the acCmdZoom method on a textbox control on a continuous form. It opened the zoom, then gave me the same error several times, likely reflecting the number of records on the form, (i just hit the esc key to scroll through all the errors quickly.)

Any thoughts would be much appreciated,

*** UPDATE, Ok, I just did a Linked Table Manager again, got the error 112 times (or so), which is nowhere near the number of tables linked. After the error occurs once, and you perform that operation again, the error does not happen. Same thing when zooming, the error occurs sever dozens of times, but when i perform the zoom again, it does not repeat the error.

STILL NEED HELP

~Scott

View 8 Replies View Related

Unexpected Behaviour In Sub Form

Dec 24, 2004

Why does my sub form which is contained in a Tab, not display unless a certain field is populated with something? It's not a required field. Infact it is one of two identical fields... a relationship and a lookup to another table. It doesn't matter if the second one if filled in or not. Just this one. The record is in the database, I choose from a drop down in the header... and the subform then requeries and displays the information. However, unless this 1 field is filled in.. the subform remains blank. It is getting on my nerves!!

View 1 Replies View Related

Report Prints The Same Number Of Times As The Number Of Records

Jul 10, 2006

I've created a report and report has the same number of pages as the number of records that it's displaying.

If there are two records, the report has 4 pages...the first 2 are the actual report and then the other 2 are a copy. If there are 3 records, the report would have 3 copies (...6 pages).

Any idea how to change this so that I have only one copy of the report?

View 1 Replies View Related

Producing Outlook Email Thru Access

Apr 29, 2005

I have code to open a new email and populate the BCC field. My problem is the company usually uses a template for all emails they send, from access how to I het outlook to use the the usually template?

Thanks

Paul

View 4 Replies View Related

Unexpected Row Inserted In A Code Table

Jun 22, 2005

I have the following tables, tblAudit and tblCriteria. The first records companies and period of audit, the second contains the criteria against which they will be audited. The audit period determines which criteria apply.
Results are recorded in a third table, tblAuditResults.

Relationships
tblAudit 1:M tblAuditResults M:1 tblCriteria

My problem is when I try to add a new result by selecting a criteria via a combo box a new row is inserted in the criteria table as well as in the result table. Inserting a row in the criteria table is wrong.

I have attached the database for you to look at.

Thanks for any advice you can offer.
Fearless

View 3 Replies View Related

Quey Producing Out Of Bound Results

Apr 6, 2006

Hi all,

I am performing the following query using an ADO connection from Excel;


SELECT [Date], SUM([Batch Qty]) As [Batch] FROM [Speed Fastener Packing] WHERE ([Date] BETWEEN #03/04/2006# AND #05/04/2006#) GROUP BY [Date] ORDER BY [Date]


When I execute this statement in Access it generates The following;

Date Batch
04/03/20063463000
06/03/20062747000
07/03/20062738000
08/03/20063867000
09/03/20064970000
10/03/20066235000
11/03/2006757000
13/03/20062170000
14/03/20063864000
15/03/20066929000
16/03/20063342000
17/03/20067017000
18/03/20063440000
20/03/2006985000
21/03/20064330000
22/03/20067578000
23/03/20064367000
24/03/20065003000
27/03/20063726000
28/03/20064383000
29/03/20066367000
30/03/20064358000
31/03/20064840000
01/04/20063136000
03/04/20061102000
04/04/20063000000
05/04/20065724000
06/04/20061687000

Why is the statement returning results that are not between the dates specified?? The [Date] field in the Db is in format Date/Time Short Date dd/mm/yyyy.

Dont have a clue what I'm doing wrong! TIA, any ideas appreciated!

View 2 Replies View Related

Reports :: Unexpected Field Format

Mar 21, 2015

A report field data is sourced from a query which displays its values as a string eg "George Renee", however in the reports it displays data as a number eg 42. It is true that the query field obtains its data from a table which gets its data through lookup Wizard. I really want the report to display string type.

View 1 Replies View Related

Forms :: Producing Serial Numbers

Sep 7, 2013

I would like to produce a serial which will have 4 members.The first, the second and the fourth members will be always CAPITAL Letters and the thirth one is always NUMBER.Here is an example for the format: AC3H

Each member will change in any line and never match... I would like to produce it by a button or a button for each one.The last important point for this issue is; I would llike to identfy the total number of serials.

View 3 Replies View Related

Unexpected Error From External Database Driver

Jun 14, 2006

I am getting this error message:
"Unexpected Error from External Database Driver" when I try to import some .dbf files into Access. I just did some quick research, and it was suggested that I remove or rename the borland driver. Not sure how I feel about attempting that, plus, where do I get a new borland driver once the old is removed/renamed.

View 1 Replies View Related

Complex Query/form Results Unexpected

May 16, 2006

I've got a query pulling data from a view I created that gives all the data I need. The query gets it's criteria from a form with 10 combo boxes, a radio box with 3 options and 2 date fields for the timeframe to run the report in.

The issue:
over half the columns of data that the radio buttons can limit results by have null values but only two of the columns are excluding records where the values in them are null. As any combination of the combo boxes are null I'm using the following format for my criteria per column
IIf(IsNull([Forms]![Margin Dialog]![InstallerOption]),[Installer_from_Web],[Forms]![Margin Dialog]![InstallerOption])
That is an example of a column that works, the following is a column that is excluding null values no matter what I do
IIf(IsNull([Forms]![Margin Dialog]![AOIOption]),[ProductType],[Forms]![Margin Dialog]![AOIOption])
I am not seeing the error and could use some thoughts.

View 13 Replies View Related

Forms :: Unexpected Data Retrieval In Form

Aug 31, 2014

I have an entry Form which is used to record prospective new members of a society in a Table named 'Foreigners' in a Membership Database using Access 2010. The entry form also records the name of the 'Interest Group' to which the prospective member is initially affiliated.

On entering this Group Name, selected from a drop down list from the Groups Table, The Group ID & Leader Member ID are automatically recorded.

However, I don't wish to record the Leader Member ID, what I wish to record is the Leader's Member Name which is identified by that ID in the related 'Mail List' Table.

In the Properties sheet the relevant source for this field is shown as Leader, but what is displayed is the Leader ID...

View 5 Replies View Related

Reports :: Producing A Report And Then Saving As Attachment

May 29, 2013

I am building a database which produces a report which I have formatted as a letter, I know I could have done this via a mail merge etc however...

What I am wanting to achieve is when this report(letter) is run, is there a way that when it is produced from a macro button embedded in a form that this report can then be saved as a pdf attachment within the same access database attached to a client record?

View 3 Replies View Related

General :: Unexpected Syntax Error On Update

Sep 19, 2012

I have a website which uses a series oif ASP/VBscript scripts to access and update an Access database. It has run for years on a shared commercial service.I also have a replica of the wesbite on my home pc for development and testing running under XP Pro and IIS. All works fine.

Recently I have had to switch my home setup to my laptop (also XP Pro and IIS). The scripts all appear to work ok except when I try to UPDATE certain tables.

Some give "Syntax error in UPDATE statement."
Some give "Data type mismatch in criteria expression."
Some work without error

Yet the identical script and table on the main site and my home PC work fine and have done for years.

View 14 Replies View Related

General :: Select Query Producing Inaccurate Results

Mar 6, 2015

I have a query that is returning inaccurate "Date Completed" results. Here is the SQL:

PARAMETERS [Enter Start Date] Text ( 255 ), [Enter End Date] Text ( 255 );
SELECT DISTINCT [Qry BDM FA English Email Test Part 1].ADI_DISTRIBUTOR_ADVISOR, ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_DATE_COMPLETE D, [Qry BDM FA English Email Test Part 1].AGA_EMAIL, ADL_AAH_ADVISOR_ACTIVITY_HISTORY.AAH_EMP_ID, DateAdd("d",180,Date()) AS [Next Update],

[Code] .....

View 4 Replies View Related







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