combining two queries

I have a query that finds the running sum of Inventory
using the dsum function.  I have another query that
finds the running sum of Accounts Receivable again
using the same method.

I have a list box on a form showing the data based on
the inventory running sum query.  I would like this
list box to have the AR data in it also.

Is this possible? 


0
tom
10/30/2007 4:33:21 PM
access 16762 articles. 3 followers. Follow

2 Replies
531 Views

Similar Articles

[PageSpeed] 9

Tom,

I'm assuming that what you mean by a running Inventory and running Accounts 
Recievable is that these queries have a date field, and sum some other field 
(or combination of fields) for all values before that date, for a particular 
product.  Is that a valid assessment?

Can you give us the field name in your queries and a couple of sample 
records from each.  If my assumption above is correct, then the critical 
question is "are you certain that you have an entry in each table for each 
date in the range you want to display?"

Dale
-- 
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


"tom" wrote:

> I have a query that finds the running sum of Inventory
> using the dsum function.  I have another query that
> finds the running sum of Accounts Receivable again
> using the same method.
> 
> I have a list box on a form showing the data based on
> the inventory running sum query.  I would like this
> list box to have the AR data in it also.
> 
> Is this possible? 
> 
> 
> 
0
Utf
10/30/2007 5:17:07 PM
This is the query I am basing the list box on.  I want
to combine this with an exactly similar one for
Accounts Receivable.

SELECT InventoryBalance.Date, DatePart("y",[Date]) AS DayOfYear, 
DSum("InventoryBalance","InventoryBalance","DatePart('y',   [Date])<=" & 
[DayOfYear] & "") AS RgBlceInventory
FROM InventoryBalance;


"Dale Fye" <dale.fye@nospam.com> wrote in message 
news:A38BC703-EB81-4A03-A7A9-69183C7BB27E@microsoft.com...
> Tom,
>
> I'm assuming that what you mean by a running Inventory and running 
> Accounts
> Recievable is that these queries have a date field, and sum some other 
> field
> (or combination of fields) for all values before that date, for a 
> particular
> product.  Is that a valid assessment?
>
> Can you give us the field name in your queries and a couple of sample
> records from each.  If my assumption above is correct, then the critical
> question is "are you certain that you have an entry in each table for each
> date in the range you want to display?"
>
> Dale
> -- 
> Don''t forget to rate the post if it was helpful!
>
> Email address is not valid.
> Please reply to newsgroup only.
>
>
> "tom" wrote:
>
>> I have a query that finds the running sum of Inventory
>> using the dsum function.  I have another query that
>> finds the running sum of Accounts Receivable again
>> using the same method.
>>
>> I have a list box on a form showing the data based on
>> the inventory running sum query.  I would like this
>> list box to have the AR data in it also.
>>
>> Is this possible?
>>
>>
>> 


0
tom
10/30/2007 5:39:51 PM
Reply:

Similar Artilces:

Two Bill Payers with one account (one bank)?
Does anyone know if this is doable, or has anyone done it? I'd be more interested in knowing the theoretical answer, i.e. does the OFX model allow this?. The scenario I'm thinking of is this: Bill payer no. one is the service offered by the bank I use. (Theoretically, a bank will never offer you more than one choice here, ... but who knows). Bill payer no. two is the service we all get (pay for, to say this correctly) with higher-end versions of Money, i.,e., MSN Bill Pay. ...

make notecards two per page inside and outside
I'm trying to make notecards that print outside and inside, two per page on embossed cardstock. Can't find template in word or publisher formats, or even Avery that will allow me to do this HELP!!!! Go to File, Page setup and create the size of your choice; also do an Insert, Page. When you go to print it will tell you it will print two copies per sheet. -- Don Vancouver, USA "Ruthie" <Ruthie@discussions.microsoft.com> wrote in message news:4AD82E0E-5BB1-48F8-81ED-91FA9CDA01EE@microsoft.com... > I'm trying to make notecards that print outside and insi...

open a message box if a query is null during an autoexec macro
Hope someone can help with this. I have a query that runs using an AutoExec macro when the database opens. What I want is for a message box to open instead of the query results window, telling me if the query produced any records. I can do this easily enough through a command button on a form, but cant figure out how to do it through an autoexec macro at startup. Any suggestions? You need to have your Autoexec macro run a VB function (macro action RunCode)that executes the query and captures the count of records retrieved. Dim strSQL as string Dim k as integer Dim rs a...

how do I create a formula to seperate name into two cells (last, .
Is there a formula to seperate a name in one cell (last, first) and make them into 2 cells? Hi Jobby, You could use tht Text To Columns feature. With the name cells selected, try: Data | Text To Columns | Delimited | Next | Select appropriate delimiter | Next | Next | Finish --- Regards, Norman "jobby55" <jobby55@discussions.microsoft.com> wrote in message news:7F9D3F16-EA31-4435-A659-BF8F836A9F01@microsoft.com... > Is there a formula to seperate a name in one cell (last, first) and make > them > into 2 cells? The most direct way is to use &quo...

Comparing data on two worksheets and producing a exceptions listin
I wish to list all new or modified data fields ( approx 60) to a worksheet over a given periods. At this stage my plan is to create a master (as at dd/mm) version m1, then update current worksheet with new/ modified data and compare with my master. I'd like to produce an exception report detailing all the changes as an audit check. Ideally this could be automated ...

Split Long Text Cell into Two Shorter Cells Without Splitting Word
I have text cells that are up to 60 characters in length that I need to break into 2 separate cells, each cell can be up to 30 characters long without splitting up any words. So, if the 30th character splits a word, it should split to the second cell at the previous space. How do I set up a formula to do this? I figured that if I wanted the first 30 characters, I could use the formula (if the text was in A1) =LEFT(A1,30). But this splits any words. Thanks in advance! Of course you cannot. If you have 60 characters, and you split them at character 25 you will have 35 characters in ...

How to Run query based on txtbox value that is part of control source
Hi guys I've created form using wizard. Later on i created listbox where I want to show data based on my quiery SELECT EmpEquipmentSkills.EmpNum, EquipmentTraining.Equipment, EmpEquipmentSkills.Status FROM EquipmentTraining INNER JOIN EmpEquipmentSkills ON EquipmentTraining. EqNo = EmpEquipmentSkills.EqNo WHERE (((EmpEquipmentSkills.EmpNum)=[Forms]![Employee1]![txtEmpNum]) AND ( (EmpEquipmentSkills.Status)="x")); That is assigned to row source table/query type And in VBA I have added Event : Private Sub txtEmpNum_AfterUpdate() List20.Requery End Sub But when I start for...

Tennis set combinations
I would be grateful for help with listing all the possible gam combinations within a set of tennis. I just need to list all the possible combinations of which player (1 o 2) wins each game. So for 6-0 to a player it is obviously 1,1,1,1,1. 6-1 can be 1,1,1,1,1,2,1 etc Is there a way excel can list all the possible combinations? Even if I just have a combination of all the ways 1 and 2 can win in 12 game period. I guess it is just 2*2*2*2*2 etc but how do I list it in 1s and 2s? Hope this is clear -- Raigmor ----------------------------------------------------------------------- Raig...

Basing a query on the selection in a Combo box parameter
I have a group of engineers polling servers to learn what Middleware is running. I would like to give them a dataset to update as they get the info from the servers. I created a query that asks for a middleware type, and returns the servers that run that middleware. But I realize that there is a difference between WAS and Websphere, and expect users to make that mistake. To me the obvious answer is to force users to select from a fixed list in a combo box. I used Access Help, and found a topic for placing calendar controls in a form to drive the criteria for the query, and mod...

random incomplete query results
Hi to all, on a form I'm filling a "box" (I don't know the english name, is like a combo without the input selection row) from a table on the same database. To do that, I'm using the property "RowSource", also to change the ordering field. The problem is that, when I'm using the database on a PC with Windows 2000 and Office 2000, "sometime" (exactly! not all the times!) the list on the box is covering only a fraction of the entire table! After some trials I get the full list... The same database, on my PC (XP and Office 2003), is working alw...

set nocount on and ad hoc queries
Hi All, We are running an application with a lot of ad hoc queries in terms of thousands per second (they are prepared). My question is that, if we append SET NOCOUNT ON to each and every ad hoc query, will there be any benefit of doing that? I understand it will help stored procedures... we are running SQL 2005 64bit EE on Windows 2003 64bit EE. Application is programmed in Java. Thanks very much, -- Brian ...

vlookup combined with INDIRECT #2
thanks Frank - I will give it a try -- Wes ----------------------------------------------------------------------- West's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1612 View this thread: http://www.excelforum.com/showthread.php?threadid=27637 ...

Inline comments in Access queries
How to add inline comments to access queries.? On Thu, 21 Jan 2010 21:26:22 -0800, Susanna <Susanna@discussions.microsoft.com> wrote: >How to add inline comments to access queries.? > Alas, you cannot: an annoying restriction of the program! Microsoft has been hearing complaints about it for years but AFAIK has still done nothing to solve it. There are some kludgy getarounds, e.g. SELECT ... <a bunch of stuff>, "Put your descriptive comment here" AS IgnoreThis, ... but it's pretty inadequate. -- John W. Vinson [MVP] ...

Unmatched query
I am trying to set up an unmatched query but it does not work. I have two queries and i am trying to match off with stock codes. The first query has all the stock codes already in it (and no stock names). The second query (only has stock names) adds the stock codes which it sources from a table that contains both stock codes and the stock names. Any idea why there is no output with this unmatched query. Thanks George George: A possible reason is that you are placing a restriction on the second query in addition to the IS NULL criterion. The combination of an IS NULL key and a value i...

Problem retrieving data using queries in a simple (one table) data
I've been asked to create queries (for example all songs by an artist) in a CD Collection database. The query can only retrieve some songs for some artists and no songs at all for other artists. I've checked the query which seems ok. I think the problem could be because it is a simple database (just one table with about 50 columns and about 100,000 records. I tried to use "Analyse --- Table" Tool but have not been successful. To avoid corrupting the database, I've decided to get advice from you wonderful people out there before doing anything else. Can I change th...

filter a table based on a query
What I really want to do is update a table when another table has been edited. I have a table of attendence, and another for registration that the table attendence is created from based on an Append Query. I managed to create a delete query for when course are cancelled to delete records in the attendence table, but when an individual cancels a course, I'm having trouble figuring out how to delete because this query and table are not linked directly. So I was thinking alternatively of writing a macro and applying a query to filter my table and then working out that all 'present' fi...

query functions
I have a large amount of 4 column data in an Excel file. Without using Access, I want a simple way for a user to enter a number, execute a search and have the search paste the row of information found next to the search entry. The data itself will be hiden so the user only sees the search entry and the results of each search. Dean You can do this with a vlookup function. If the hidden data is on Sheet2, in say A1:D25000. You wanted to look up an entry in A1 on Sheet1. Returning the value in columnB on sheet2 corresponding to the 'found' entry, then in B1 Sheet1 enter =IF(I...

Compare Two Workbooks
I'm trying to write a piece of VBA that will check each cell in a workbook against each corresponding cell in another workbook, and highlight where there are differences. I'm really falling over at the first hurdle, as I cant get my head around how to reference the cells. I've got variables that tell me the workbook, the worksheet, the row and the column, but I don't appear able to so something as simple as check if wb1.ws1.cell1 = wb2.ws2.cell2. The code I have so far is below: Sub test() Dim wb1 As Workbook Dim wb1name As String Dim wb2 As Workboo...

EXCEL and Query
Microsoft Query won't give me access to data in a Microsoft Excel list. After connecting to the data source (another EXCEL spreadsheet) the error message 'No tables visible' is displayed. I have named the source spreadsheet as described in the on- line help, but still no result. Is there something else I need to switch-on? Help please. Have you tried giving a name to the table in the source spreadsheet. Eg. Data is in A1:F2500 - highlight that area and go Insert / Name / Define.... call it MyData - that table name should become visible when you do the query from the ot...

make a query from several others or....
I have an excel report which draws data from access. The excel table looks like this: Each project is reviewed by two people Reviewer Name | Reviewer1 | Reviewer2 | Total last 12 months AA x y x+y This is repeated for 3 months and 1 month (so the querry uses a date between to work that out) x is a create-tabel query and so is y. I want to create one table for these queries (in total 6 per reviewer) and import that table into excel. I would also need to add the reviewer 1 and 2 in the table so as to import it in one go. Can you help ...

Append queries
I have a database that someone else built, and I am building reports for. They want a graph put on a report and the table is set up with multiple columns with the names of the data type (i.e. Description 1, Description 2, etc.). I know that I can only have two columns of data for a graph (pie chart), and I know that I have to create an append query to update values to another table because the graph does not like that my report data is filtered by criteria. How do I make the append query I am trying to create use the Description 1, Description 2 fields as data values. Basi...

mulltiple column "not in" query
Is it possible to perform a multiple column "not in" query in access? I created the query select * from climktvaluefromgroups c where (c.group, c.invdate, c.marketvalue, c.clicode) not in (select t.group, t.invdate, t.marketvalue, t.clicode from tempclimkt t) and I got the error: You have written a subquery that can return more than one field without using the exists reserved word in the main query's from clause. Is this not possible? You cannot do what you are attempting. You MIGHT be able to use concatenation like this: select * from climktvaluefromgroup...

how do I combine cells (names)
I have first names in Column A and last names in column B. How do I combine them? If A1 is Gary and B1 is Weaver then put =A1&B1 in C1. -- Gary's Student "fiji41" wrote: > I have first names in Column A and last names in column B. How do I combine > them? Make it readable =A1&" "&B1 <G> -- HTH RP (remove nothere from the email address if mailing direct) "Gary's Student" <GarysStudent@discussions.microsoft.com> wrote in message news:F60C3468-5F0C-41EC-A315-60295C9DBF13@microsoft.com... > If A1 is Gary and B...

Monthly Report by SQL Query
How do you query in RMS database so that you can output monthly breakdown of Sales/Cost/Profit to Excel? I don't want to use Quickbooks for reports. It would look like: Jan Feb Mar Apr... --------------+-----+-----+-----+--- Sales 500 530 530 350 Cost 200 203 398 129 Profit 300................... Thanks! The following instructions are vague, but you will be able to figure it out. create a new datasource pointing to your RMS database. start excel and create a new pivot table using external datasource. follow "wizard" customize query in M...

Update Record Performance
I currently have C++ code that processes a raw data file containing multiple types of records. The code creates records in multiple tables in an Access database depending on the record type. The records are created one at a time as the data is read. I don't think this is very efficient. But I can't change this part of the processing. There are hunderes of thousands of records in raw data and the tables. I now need to update fields in created records from data contained in other tables in the database. The options I've come up with are: 1. In the C++ code, read t...