Reply:

Adding running total to union query
I posted this before and thought I had a solution. Unfortunately, it became too large and I received a "Query is too complex" error, so now I'd like to try a different approach... I have created a Union Query to consolidate results from separate queries, each one of which represents results for a single month: SELECT ReportMonth, MonthlyRev, MonthlyCost FROM qryMonth1Total UNION SELECT ReportMonth, MonthlyRev, MonthlyCost FROM qryMonth2Total UNION SELECT ReportMonth, MonthlyRev, MonthlyCost FROM qryMonth3Total; etc. (12 months total) This gives me the following results: ...

sorts query while including <all> at top of list
I have a combobox whose data source is this query, which sorts the records correctly: SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject ORDER BY IIf(InStr(FixtureTypesNoProject.Type,"-")=0,Len(FixtureTypesNoProject.type),InStr(FixtureTypesNoProject.type,"-")-1), IIf(InStr(FixtureTypesNoProject.type,"EM")=0,[FixtureTypesNoProject.Type],Replace([FixtureTypesNoProject.Type],"EM","0")) ; however, when I add the following (to include the <all> word at the top of the combolist...) it no longer sorts UNION Select &q...

Can't see what is wrong with this Union query...
Can you? SELECT "Fred" AS Name FROM tblMonths UNION ALL SELECT "Joe" AS Name FROM tblMonths; Your union query is not required to pull any data from the table. Maybe you want something like this that does not need a union query -- SELECT tblMonths.* FROM tblMonths WHERE Name = "Joe" OR Name = "Fred"; -- Build a little, test a little. "AndyK" wrote: > Can you? > > SELECT "Fred" AS Name FROM tblMonths > UNION ALL SELECT "Joe" AS Name FROM tblMonths; > AndyK wrote: > Can y...

Alter query for report?
I have created a database that pulls a text file into a table, cleans it out, and then displays in a report with appended comments that my users can alter. Now I have a user request for the option of selecting how many days out worth of data they want the report to display. I have a query pulling the main table in with the address and supplier names being pulled from other tables. That's where I have the report generating from. I know that under the date field (which is already in the date/time format) I use the line : <=Date()+"*" with the * representing any number of d...

New to Access, need some help with a query
I need some help. I have basic Access experience and cant, for the life of me figure out what im doing wrong. Currently I have two tables: Table 1 with Fields: Category ID Sub Cat ID Product Term ID Product Name Description Table 2 with Fields: Field1 Field2 Field3 Field4 Field5 Field6 Field7 What I’ve been trying to do is create a query that will allow me to show a datasheet with specific changes to data and only certain fields. I need to see a datasheet like this: Field2 (from Table 2) Category ID (from Table 1) Field4 (from Table 2) Field5 (from Table 2) Field6 (from Table 2) Field1 ...

Append Query help 02-18-08
Can Some Help me out with my Append Query or help me with a better way to do what im achieving. table one is Clients and table two is Suppliers. to each table i have related table client contacts and SupplierContacts which allows me to have , multiply contacts for each company. any way on the associated tables i have a tick field called Buzzsaw members and what i want to achieve is to have a table that shows combined client Buzzsaw members and all the supplier Buzzsaw members So What i did was created two Append Query's that append to a new table tbl_Buzzsawmembers and on the criteri...

Tab control not working with queries
I have a form with a tab control on it. Within one of the tabs I have a subform. the problem I have is that I have a button on this subform that runs a report based on a selection within this subform. Bottom line is it appears that the query (that the report uses) doesn't recognize the criteria - [Forms] ![F_AFE_subform]![AFE_ID] - because it is not part of the main form that the tab control is on. Is there a way to accomplish this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 "szag via AccessMonster.com"...

E-mail query to recipients in table
Hello, I have created a database in Access 2003 with a table of suppliers and a table containing my orders with those suppliers. Now suppose I want to send out lists with outstanding orders to those individual suppliers as a sort of reminder, only containing their "own" orders. I have written following code: --- Dim rs As Recordset Set rs = DBEngine(0)(0).OpenRecordset("tblSuppliers") Do While Not rs.EOF If DCount("[SupplierID]", "qryOutstanding", "[SupplierID] = '" & rs.Fields(0) & "'") <...

Can there be too many sub queries within a single query?
I am trying to combine the results from more than 15 queries into a single query in an attempt to save opening 15 different queries individually. The query runs successfully with the exception of the 15th, or last, query returning null cells. If I pull that sub query by itself it does return the correct data however. Has anyone experienced this in their projects? Thanks for your help, Rick I do not know as there is a limit to the number of subqueries but I read there is a limit to the total number of characters in the complete SQL statement. That would include subqueries,...

Filter Parameter For A Query. Dependant ComboBoxes
I have two forms. Form I consisting of a field "Clients" & "Products". Form II consisting a similar fields "Clients" plus "Products" in form of ComboBoxes. I want the Form II "Products" Combo Box to be reflecting only those as per the Form I which pertain to the "Client" Selected in the first combobox. Please see if you can help me, specially by giving me a source of a similar sample file. -- Thanx in advance, Best Regards, Faraz "Faraz A. Qureshi" <FarazAQureshi@discussions.microsoft.com&...

populate a DataSet via a LINQ query (Linq to XML)
Is there a way to populate a DataSet via a LINQ query? (Linq to XML) Instead of; DataSet ds = new DataSet(); ds.ReadXml(myfile.xml, XmlReadMode.InferTypedSchema); dataGridView1.DataSource = ds.DefaultViewManager; dataGridView1.DataMember = "myNode"; Anthony Anthony wrote: > Is there a way to populate a DataSet via a LINQ query? > > (Linq to XML) > > Instead of; > DataSet ds = new DataSet(); > ds.ReadXml(myfile.xml, XmlReadMode.InferTypedSchema); > dataGridView1.DataSource = ds.DefaultViewManage...

hierarchical data
Now that Jet is being developed in-house by the Access dev team can we expect to see some movement on its querying capabilities. I for one would love to see some functionality to better aid in representing hierarchical data. SQL Server 2005 introduced "Recursive CTE's" will Access follow suit with a similar concept? If I could request only one future feature this would be it! ...

Getting query data for a report
If I have the query "UniqueCounts" and following query results: Count ServicesType 2 Food Basket 99 Men's Dorm 84 Personal Needs 5 PN Family Pack 14 Program Dorm 7 Temporary ID What would I put in a text field in a report to get the query results from Count for the ServicesType "Men's Dorm". (Which should be the value 99.) Thanks! you would want to put in 'Men's Dorm' as the criteria in the query....on in a new query that is applied to this query ...

problem in a form from crosstab query
Hi, I have a form from a crosstab query. On that form, I've added some fields to make "on the fly" calculations but they remain blank if one of the values returned from the query and used for calculation is = 0. What I can do? Thks in advance Use Nz() around each field, to replace null with zero. For example, instead of: =[A] + [B] + [C] use: =Nz([A],0) + Nz([B],0) + Nz([C],0) Here's another suggestion for getting a row total in a crosstab: http://allenbrowne.com/ser-67.html#RowTotal -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Acces...

Query for a date, any date
Hello: This seems too simple, but I just can't see how to do it. I need to query for a date, any date. As long as MM/YY or MM/DD/YY or MM/DD/YYYY appears with any date in a feild I need it to come up in a query. The dates are mixed into a name feild. For example I'm currently also querying for names that have "144A" in them. So if a Name in the table is "Westinghouse 144A" is will appear in the query. I expect values such as "Westinghouse 04/09/2009" to come up. I want these to come up in the query. The exact date does not matter. If it has any dat...

Not getting the data output I want
Hello, I need help with my database. Here's the basic layout: I have a table for patient data that is imported on a daily basis. That data is then queried to eliminate null values and the query is the source for my main form. I then have a subform based on a table with procedures that has no data in it. It is strictly for data entry to include the date of service and checkboxes for procedures rendered. I used the PtID (auto number) as my primary key in my main form and the same name for a foreign key in the subform. Those fields are used as the link parent/child fields. I ...

Combining two types of functions. Select Query and DLookup
I would like to write a code that performs the following action: 1. If the value in a comboBox Project Number is 19912 2. Perform a select query on comboBox Task Number and comboBox National Site ID 3. If the value in a comboBox Project Number is not 19912 4. Perform a select query on comboBox Task Number and a DLookup () on comboBox National Site ID I tried the code below but it's not working. Any ideas on how to do this? If Me.cmbProjectNumber.Value="19912" then cmbNationalSiteID.RowSource = "SELECT DISTINCT [National Site ID] FROM [InScope Table] WHERE [Proje...

Link a Query
Hello, I want to link a query from a seperate database into my database. I found how to link a table, but can't find any way to link queries. Can this be done? Thank you On Wed, 19 Mar 2008 08:38:01 -0700, Nightshade <Nightshade@discussions.microsoft.com> wrote: >Hello, > >I want to link a query from a seperate database into my database. I found >how to link a table, but can't find any way to link queries. Can this be >done? > >Thank you Not to my knowledge. You can use File... Get External Data... Import to import the query, or copy and paste th...

How do I compare output data from 2 pivot tables in a graph forma.
I have a pivot table created for 2004 that shows the number of inspections we received on a particular day of the week (ie. 5 on saturdays, 2 on wednesdays, etc.) I am creating the same pivot table for 2005 and am looking for a way to graph the data (bar graph) of the pivot table from 2004 to compare with my 2005 data (ie. last year we received 5 inspections on saturdays, this year we received xx amount on saturdays). Is this even possible to do? I am using Office Pro 2003 and any suggestions would be greatly accepted. Doobi, One option is to combine the pivot tables for 2004 and...

Help needed on 3 table query
SQL 2008 I have three tables which I need to get the following result from. Can anyone help CREATE TABLE [dbo].[tbBOSS_client]( [Licence] [varchar](6) NOT NULL, [CompanyName] [varchar](256) NOT NULL, [Status] [varchar](32) NULL, CONSTRAINT [PK_tbBOSS_client] PRIMARY KEY CLUSTERED ( [Licence] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[tbBOSS_ClientService]( [Licence] [varchar](6) NOT NULL, [ServiceID] [int] NOT NULL, [AUDDISEnabl...

Screen Output printing
When a report destination is selected as screen and is displayed on the screen. The screen output window allows you to print the report - but it does not allow you to select certain pages. It will only print the entire report. I am looking for way which will allow me to select a range of pages, from 10 to 12 for example. Thanks nfp, You can save the report as PDF and then choose to print the report by page range -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "nfp" wrote: > When a report destination is selected as screen and is displayed on the scre...

Query refresh message on Excel
I mistakenly clicked the checkbox when opening my Excel file that read "Enable automatic query refresh for all workbooks and do not show this warning again". How can I get this specific message back again? I need to have the ability to choose whether the queries are refreshed or not at the time the file is opened. Can someone help? ...

Sorting Alphanumeric values in query/report.
In my database I am trying to sort my companies part numbers. They contain both letters and number, for example 22D10-2, 21BC123, 25TD47, 38WS101 or 21D10 are some of the part numbers. In my products list table they sort in the correct order, which is first by number then by letter then by number again, but I created a query, which I'm using for a report on our Booked Sales for the year, and I want to sort it first by the month then by the part number. Everything sorts fine except for a group of a few part numbers which should be further down in the list. These mis-sorted number...

Do you need MS Access to query on an Access table?
I'm asking these questions because I have looked at a lot of stuff in the discussion groups and still confused. I am fairly good at Excel programming but haven't been able to get Excel and Access to talk. First, I have Office 2003 Professional at work and Office 2003 without Access at home. I want to work on developing Excel programming at home which will get data from Access to Excel with either MS Query or with programming. Is it possible to just have the .mdb files on the home computer for Excel to work with, or do I need the Access program too? The info I want ...

A07: multiple query export to excel
In A07, any ideas on how to export multiple queries into the same excel sheet with a single click as I do now in A03? The boss needs them on one sheet. They have the same columns. Together they will always fit on one sheet with plenty of room to spare.Now, in A03 I OutputTo Excel a main REPORT that just contains 2 subreports that are each based on a query. The first subreport grows and shrinks such that the second subreport tucks itself nicely just below the first subreport's last row. The subreports and underlying queries are setup column wise to be the same such that with a single click ...