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.
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;
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
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...
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],
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?
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;
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!
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.
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?
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.
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?
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.
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.
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.
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.
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.
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?
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?
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?
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!!!
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??
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?
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".
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:
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....
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?
