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
SELECT ReportMonth, MonthlyRev, MonthlyCost
SELECT ReportMonth, MonthlyRev, MonthlyCost
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
SELECT FixtureTypesNoProject.Type FROM FixtureTypesNoProject
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...
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 --
WHERE Name = "Joe" OR Name = "Fred";
Build a little, test a little.
> Can you?
> SELECT "Fred" AS Name FROM tblMonths
> UNION ALL SELECT "Joe" AS Name FROM tblMonths;
> 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 :
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:
Sub Cat ID
Product Term ID
Table 2 with Fields:
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
any way on the associated tables i have a tick field called Buzzsaw
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
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
"szag via AccessMonster.com"...E-mail query to recipients in table
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,
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
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
Thanx in advance,
"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)
DataSet ds = new DataSet();
dataGridView1.DataSource = ds.DefaultViewManager;
dataGridView1.DataMember = "myNode";
> 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
to see some movement on its querying capabilities.
I for one would love to see some functionality to better aid in representing
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:
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
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
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]
=Nz([A],0) + Nz([B],0) + Nz([C],0)
Here's another suggestion for getting a row total in a crosstab:
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Acces...Query for a date, any date
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
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
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
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
On Wed, 19 Mar 2008 08:38:01 -0700, Nightshade
>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
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
One option is to combine the pivot tables for 2004 and...Help needed on 3 table query
I have three tables which I need to get the following result from. Can
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
)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.
You can save the report as PDF and then choose to print the report by page
> 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 ...