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 Replies


ADVERTISEMENT

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 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

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 3 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

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

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

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

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

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

Reports :: Printing Labels - Report Producing Blank Page

Aug 13, 2015

I have a non standard size report (for printing labels)

The report is just over 10cm wide and just over 15cm deep.

I have solved the blank pages caused by exceeding the width but I'm still getting a blank page when I move some data down. I am still within 15 cm but it's throwing me a blank page. Even if I extend the in design mode depth to say 20cm (ie the height in Detail) it's throwing a blank page (I can see this in print preview).

I initially set up the report (it's a long time ago so memory fades) using the label wizard setting up a label 4*6 inches (10.16 * 15.24 cms) and it is this i'm trying to tweak. Is there a property I'm missing somewhere?

View 5 Replies View Related

General :: Empty Date Field Producing Error When Add Button Pressed

Aug 20, 2014

When user enter the data other than date field or when date field remain empty and user press the Add button, it is giving error.

(Error 3075)

Syntax Error in date in query expression '#'.

while same code is working for text field. but it is not working for date field.

If (Me.rdate) = "" Then

Exit Sub

End If

View 14 Replies View Related

Getting Query Criteria To Select All Records Or Specific Records In Query Design Section?

Jun 16, 2014

How can I get a Query Criteria To Select All Records or specific records in query design section.

I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.

View 2 Replies View Related

Query Problem: Records Linked To Records

Oct 31, 2007

Hi,

I was wondering if anyone would be kind enough to help me out on this problem I have.

I have a table:
---------------------------------
Source | LinkedTo
---------------------------------
A1 B2
B2 C1
B2 C2
C1 D1
C2 D2
C2 D3
B2 D4
A1 D5

Another table:

-------------------------------
Item | Load
---------------------------------
D1 2
D2 4
D3 6
D4 8
D5 10

So looking at the tables,
C1 = 2
C2 = 4+6 = 10
B2 = C1 + C2 + D4 = 20
A1 = 20 + 10 = 30

Here, there are 4 levels that I have to go into to get the grand total. If I did not know how many levels there were, how would I be able to do this through code? through queries if possible??

If anyone could help, that would be great! Thank you in advance.

Bear

View 3 Replies View Related

Queries :: Query That Will Return Records From A Table That Have Related Records In Another Table

Mar 4, 2015

I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.

View 3 Replies View Related

Show All Records From One Query And The Sum Of A Field In A Related Query Record

Mar 8, 2005

I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.

That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.

I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.

What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.

Can someone see where I’m going wrong?

Slaughter
slaughter at mizzou dot edu

View 9 Replies View Related

Queries :: When One Sub Query Has No Records Entire Main Query Is Blank

Oct 1, 2014

I have a query with three sub queries, all returning a number from different tables. But when any one of the sub queries returns a blank, the entire main query is blank.

How do I stop this from happening? I have tried NZ() on the sub queries and on the main query, but the blank still happens.

View 1 Replies View Related

Queries :: Differentiate A Query Based On All Group Records Or A Query Based On Only One Record

Dec 22, 2014

I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?

View 14 Replies View Related

How To Have ALL Records Appear In A Query??

Aug 8, 2005

I'm joining two tables in a query. I need to have all names from one table appear in the query results, even if those names don't appear in the joint.
I checked the second option in Join Properties to show ALL fields from that table, but when I run the query I only get the names where joined fields are equal. Changing the Join Properties doesn't seem to have any effect on my results. Any ideas what else I can do to show ALL of those names?

Thank you for any help.

View 13 Replies View Related

Query Has No Records

Mar 16, 2005

How can I set up a form to NOT open if the underlying query has no data? This feature is nicely available in reports, but how can I do it with forms, too?

Thanks in advance!

View 4 Replies View Related

Query For Every 10 Records

Oct 10, 2004

i want to make it so that on a query search, it shows me every 10 records of my "Number" field
e.g. so that it shows me record 1, 11, 21, 31,etc. Plz can someone help!!!

View 1 Replies View Related

Records Being Removed From Query

May 26, 2005

Hi

i have a query that shows 190000 records made up from related fields spread over many tables.

now i add in a new field to the query. It is related in the same way the others are, and indeed the information now appears in the query matched up correctly.

But for some reason the query now shows 670 less records?????

why is Access removing some records because of this new field??

can anyone help?

thanks

Stephen

View 1 Replies View Related

Grouping Records Using A Query

Aug 2, 2005

How can I group records based on values within a particular field? For example, if I have records of people with different annual incomes, what is the easiest way to allocate the records to income bands? I assume the starting point will be to create a band classification in Excel and import as a table. How do I subsequently run a query to achieve the desired effect?

View 1 Replies View Related

Limit Records In Top N Query

May 13, 2005

I have a Top 10 query (it is based on $ amount) and everything works fine except...say I have 1,000 records and 5 are >$0 it shows those fine but since the other 995 records are $0 it shows "all" of them.
I would like to limit the record count in the query to only 10. Within that 10 records if there are $0 records then I would like to show them up to the 10 record limit.

I see both sides I think but you would think that if you say "Top 10" it would be "10".

Thank you :confused:

View 6 Replies View Related

Numbering Records In Query

Aug 11, 2005

Hello, I have a question. I don't really know much about access, but I am really comforatable with SQL (DB2 and MySQL Programmer). Anyways, I was wondering if there is any way to do the following in SQL.

I have a Students Table that holds the names of students, an applications table that holds different applications, and an offers table that holds offers for the different applications.

The relationships for the tables are :
One Student Has Many Applications, One Application has Many Offers

IE: One Application can have up to three offers attached to it. I have the following query running to get all of the offers for all students in a specified term:

SELECT DISTINCT Offers.tblStudentProgCodeFK, Names.StudentID, Names.FName, Names.LName, (Offers.Code), tblPrograms.ProgName, Names.[Country Of Birth], Offers.[OCAS Number]
FROM qryApplications_Offers AS Offers, [Names], tblPrograms, (SELECT DISTINCT StartingDate, EndingDate FROM StartDates WHERE Term LIKE "*"+[Forms]![frmSelector]![Term]+"*") AS B
WHERE (Names.StudentID = Offers.StudentID AND (tblPrograms.ProgCode = Offers.Code OR Offers.Code=0) )
AND
(Offers.StartDate BETWEEN B.StartingDate AND B.EndingDate);

What I was wondering is if there is any way to add a field in the output that would number the selections. Right now it returns a table like:

tblFK StdID Code
34440 20394 0112
34440 20394 0123
34440 20394 0234
34234 25847 0100
47364 34857 0111
47364 34857 0311

I would like to do something like this:

tblFK StdID Code Choice
34440 20394 0112 0
34440 20394 0123 1
34440 20394 0234 2
34234 25847 0100 0
47364 34857 0111 0
47364 34857 0311 1

Where the choice column would count the choice for that student. Is this even possible? There is no choice number in the Applications or Offers table so I would need to do this in the query....


Any help would be appreciated

View 3 Replies View Related

Query Returns No Records

Aug 30, 2005

Obvious neophyte issue here, sorry...

I have a table, PURCHASE_ITEMS with 3 fields: ID, TYPE, NAME
Another table, ITEM_TYPES with 2 fields: ID, TYPE

TYPE in both tables is a text field and there is a one-to-many relationship between them.

When I run a query on PURCHASE_ITEMS, I can see all 25 records. When I set a criteria for TYPE to one of the types, no records appear, even though there are 5 or 6 of that type.

Can anyone help me with this absurdly simple problem that I can't seem to get my brain around?

View 1 Replies View Related







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