Including all tables into a query
Hi, I'm not sure whether this is the right group. If not please point me to the right one. I have a database containing a table for each month and each table contains information on the articles in the stock. I would now like to make a query which goes across the tables and draws the same number for every table. The tables all contain a product for each row and the properties of it in the columns (eg. type, number on stock, number out, number in, weight,...). All table come from the same source so they all have the same design except that a product might be in one table and not in an other one. I would now like to make a query across all tables to draw out all information. For example, I would like to know how many pieces of a specific product were sold in each month in a single table output. Or how many products of type 'type1' were there on stock each month. Or what is the average weight on stock each month. I can build the query in the design view but then I need to add every table to the query manually. Is there a way to include all tables from my database that have a specific name-format, such that all tables starting with data_* are taken into consideration? I have over 2 dozen queries and it is very tedious to make sure I haven't forgotten one. Any help is appreciated. Thanks, Tom
|
0 |
|
Thomas
8/30/2007 8:42:44 PM
access.queries
6343 articles. 
1 followers.
0 Replies
538 Views
Similar Artilces:
XmlResolver and xsl:include
Have been doing a fair amount of reading but at this point no joy. The scenario is - XSL template is stored within db. - Template has a xsl:include statement as <xsl:include href="inc.template.methods.xsl"/> I now I have to pass a resolver when I use the load method so that it understands the xsl include as in: ' object template is custom class to retrieve templates from the db dim template as new objTemplate dim myresolver as xmlresolver dim mytransfrom as xmltransform ' template.xmlDocRunTime returns a xmldocument object mytransform.load(template.xmlDocRunTim...
Pivot Table Axis format
Hi. I have a macro that creates a pivot-table and pivot chart. The X-axis is a date. In the raw data, the date is formated as month/day. This is the way I want it on the chart. However, the X-axis formats with month/day/year. How can I force this to display correctly? Thanks, Mike. Do you see a grey little dropdown under the X axis (where you can choose your dates to show)? If yes, then rightclick on it. Select the top option: "Format pivotchart field" then click on the Number button on the next dialog. There are a bunch of date formats that you can choose from. Maybe...
Outlook Rules Query
Hi group... My email rules do not work if Outlook 2002 is set to send/receive automatically on startup. However... they do work if Outlook is set up to NOT send/receive on startup and I click send/receive shortly after starting Outlook 2002 However 2... they do work if the "Run Now" option is chosen so I do know that they actually do work. Is this normal... can someone suggest the correct settings to resolve this Thanks in advance Dave G ...
Query will not sort
I have made a query and it will not sort one of my fields. I thought it was a simple query but maybe not. Field: Area, Sort Ascending Field: OrderNumber, Sort Ascending Field: Status, Criteria Like "*Y*" Field: Week 1, Criteria Is Null It will do everything except sort on the order number. -- Lenee Lenee Please post the SQL statement of your query. ?Are you saying that the query should sort on OrderNumber first? If so, why is Area first? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services ...
Extender tables #2
Hello! We are implementing an integration from Microsoft CRM into Microsoft Great Plains and this would include writing CRM fields to Extender Fields. Are there any available SDK's for Extender or maybe any table lists? Thanks, Andi Saldana There are but I think only partners can get them. Please send me a message and I'll send you what I have. "Andi Saldana" wrote: > Hello! > > We are implementing an integration from Microsoft CRM into Microsoft Great > Plains and this would include writing CRM fields to Extender Fields. Are > there any avai...
Inserting table to other table
I have a form which consists of a table. I want to insert some selected value into other table when i update and at the same time I want it to save into my own table too... May I know the way please..... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200704/1 I'm having trouble visualizing your situation. Could you provide an example of data? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner http...
GP 9.0 Table descriptions
Is there any way to find the descriptions for each table in GP? Thanks, Tony Absolutely! Go to Tools >> Resource Descriptions >> Tables. Every table is listed with all the fields. You should also consult the SDK that is on the second GP cd. Additionally, Richard Whaley has published a series of books at Accolades Publishing that does a thorough job of documenting the tables, much better than what MS offers. -- Charles Allen, MVP "Tony" wrote: > Is there any way to find the descriptions for each table in GP? > > Thanks, > Tony > > &g...
Append query and error
Hi, I use the Append query to append information from the table A to table B. I got an error message "Microsoft Office Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 13041 records to the table due to key violations." However, it works- It added 13041 records to table B- but I don't know where the errors are and how to fix it. Would you please show me what I should to do ? Please help It sounds as if you are trying to add the 13041 records again. I think that you ran the append query and it put the 13041 records in table B, then yo...
Add a field to a Make table on the fly
I have a DB with 5 tbl that all have like keys but different data. I need to be able to search the 5 tbls and return to the user all the find condition and allow them to select the one they want by dblclick. I want to add a field on the fly as I build the search query to id what tbl the record came from is there a simple way? the field can have a constant for each of the different tbl like tbl1, tbl2, etc... -- Les ...
scheduling update query to run automatically?
Is it possible to have an update query run at a set time or interval? or to have it run when ever the database is shut down? You can youse VBScript to connect and execute the update then schedule it via TaskManager or some scheduling tool. "norm" <normkara.remove@nyc.rr.com> wrote in message news:1F47C08B-0858-4B8B-939E-E904068DF3D7@microsoft.com... > Is it possible to have an update query run at a set time or interval? or > to have it run when ever the database is shut down? On Wed, 30 Jan 2008 19:15:46 -0500, "norm" <normkara.remove@nyc.rr.com&g...
In Access, How do I hide records, but include them in the total
I'm running a report where I have work orders. They are grouped by appr and comp. I need to create a report that will show all the appr tickets, but give me a total count of appr and comp. I have the report generated the way I want, with total count, but how do i hide just the comp tickets. xeoimom wrote: >I'm running a report where I have work orders. They are grouped by appr and >comp. I need to create a report that will show all the appr tickets, but >give me a total count of appr and comp. I have the report generated the way >I want, with total count, but ...
Ensuring only one commission per product in Access Table
Good afternoon, Please can someone help me. In my Access Database I have a table called tblCommission, with the fields: Commission_ID;Client_account; Product_code; Commission; Exchange (£ or $) I need a method to ensure that each client can have a commission set for every product - but that a client cant have to commission's for one product: eg: I need a method to prevent this: Comm_ID Client_account Product_code Commission Exchange 1 000001 Coke 3 $ 2 000001 Coke ...
Table Expert
When a case is resolved, there is a dropdown to select the amount of "Billable Time" for the case. I have searched tables but cannot find where this data is stored. Is there anyone that know the schema where this data is kept? Bryon, When you resolve a case, the resolution screen fields are added into the activity table as a resolution type of activity. The Billable Time value appears in the TimeSpent field. (While building my auditing application, VAST, I've had to become much more of a CRM schema expert that I ever wanted to be - by the way, you can see these reso...
Recreating HR tables
I've been having some problems with HR. I've had to create some tables so the program would run. I simply copied the tables from another compagnie where is was saying I had missing tables. I'm wondering if there is a list of Tables for HR and Canadian Payroll or if it were possible to recreate these missing tables another way? I currently have this error as well: Could not find stored procedure 'Compagnie.dbo.hrCompDoesHrCompExist' ...
How do you create a table with a shaded header in Word 2007 or 201
Hi.. Can anybody tell me, how you create a table, and then shade the first row in it to something like gray, then set the grid lines to gray or something, and maybe set the alignment in it to centered... This is 2007 or 2010, not 2003 in which it was possible to do so. Thanks. Select the first row of the table and then right click with the mouse and select Borders and shading. For the alignment, select and right click and select Cell alignment -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting ba...
How to update data in a query
One part of my database is used to keep track of contacts within clients. I created a query using a table and a crosstab query and exported it to excel. I had staff in my office update the contact information. Now I want to update the query with the new information from the excel spreadsheet. The spreadsheet includes updates to existing data and new record. How can I do this? It does not look like an update or apend query are what I am looking for. Thanks Brian On Mon, 5 Apr 2010 09:45:04 -0700, AccessIlliterate <AccessIlliterate@discussions.microsoft.com> wrot...
Including MDA information in transaction edit reports
GP 8.0 I would like to include MDA entry with the AP transaction edit list so the input can be reviewed before releasing a batch for posting. Same thing applies to our invoicing. What is the best way to accomplish this task? I have coded this once for a customer. The only method that can work seamlessly is a Dexterity customisation which uses a trigger to add extra lines into the temporary table used for the report just prior to printing. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer - Great Plains Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (f...
How to create a field in a table whose value will be calculated
I could use some help in creating a field in a table the value of which would be the continuing balance of simple arithmetic operations such as: T(n) = T(n-1) A(n) - S(n) where T=Total, A=value in a field to be added, S=value in a field to be subtracted, and n=record number Does this make sense? Slipperyrock, If I understand you correctly you want a calculated field in a table... that would be a no can do BUT you can do it in a query. Now, you can simulate a table view using a query and a form. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been den...
Pivot table subtotals 100% #2
This is what I am aiming for but how can I get 100% in subtotals for first year and the increase %age for the second and subsequent year please 2002 2003 Apples excellent 15 22.72% 16 24.23% good 21 31.82% 25 37.88% poor 30 46.46% 39 60.39% 66 100% 80 122.5% Greens excellent 12 17.65% 37 54.42% good 27 39.70% 51 74.98% poor 29 42.65% 47 69.12% ...
Table info
In Vendor Maintenance window , there are multiple Address ID's. In which table i can find this information. Great Plains 7.5 Thanks in advance Deepwater, PM00300 holds all the address information for each address ID. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports http://www.flex-solutions.com/gpreports.html "Deepwater" <Deepwater@discussions.microsoft.com> wrote in message news:F9296B22-2B44-4D64-9E3C-DC5FE4B07028@microsoft.com... > In Vendor Maintenance window , there are multiple Address ID's. In which > table i can find this...
#includes
I'm looking over my files this evening, and I'm noticing that some of my #includes (of my own header files) are in my .h files, while some are in my ..cpp files. Sometimes I have used incomplete class declarations as well. I have tried to find some good reading to help me understand this whole thing, but haven't so far. Could I get a bit of advice on this? I don't know if this is related, but one of my source files won't give me any of those "smart tips" when the cursor passes over various objects, unless it is something that I have declared in that file itsel...
copy contents including colour
Hi In cells A1:B73 I have a list of contacts which are colour coded. What I want to be able to do is to be able to enter the name of 1 of the contacts in lets say G1 and it will search for that name in A1:B73 and automatically copy its colour code over. Many thanks for any help. Al Hi, I'm assuming that the colour codeing isn't a result of conditional formatting and it's simply a fill colour. To do this you need VB. Alt+F11 to open vb editor. Double click the worksheet that this data are on and paste the code in on the right. Close VB editor and type your name...
Deleting employees
We loaded employees into Great Plains with e-connect 8.0.2.1 using taCreateEmployee and taCreateEmployeeAddress. I need to wipe this data out and re-load. I know that with the new version of e-connect there is a taDeleteEmployee but for now can someone tell me the tables I need to truncate out in order to re-run the taCreateEmployee and taCreateEmployeeAddress? thanks -- AHIhelp AHIhelp: The tables are listed below - these are the ones handled by the Employee Delete procedure so I would assume these are the ones you would need to manually handle now as well UPR00100 UPR00102 UPR00...
Table Manners
In Outlook how can I implant a table in two or more colums, to obviate the necessity to scroll down if it is a lengthy table? ...
I've inharited a spreadsheet that I need to change the query to an external source...
I've inharited a spreadsheet that connects to an external source, our SQL 2005 server. I've altered the query to reflect our server name and saved it in Excel as a query with a new name. If I press F5 I can select the range, then I click on the Edit Query icon . First it says "This query cannot be edited by the Query Wizard", I press OK. Then it says "Invalid object name "TheirSERVERNAME.odb.SVSLSITM, Statement(s) could not be prepared", which makes sense since their server is different then ours. If I press OK there it takes me to basically a blank screen/...
Have been doing a fair amount of reading but at this point no joy. The scenario is - XSL template is stored within db. - Template has a xsl:include statement as <xsl:include href="inc.template.methods.xsl"/> I now I have to pass a resolver when I use the load method so that it understands the xsl include as in: ' object template is custom class to retrieve templates from the db dim template as new objTemplate dim myresolver as xmlresolver dim mytransfrom as xmltransform ' template.xmlDocRunTime returns a xmldocument object mytransform.load(template.xmlDocRunTim...
Pivot Table Axis format
Hi. I have a macro that creates a pivot-table and pivot chart. The X-axis is a date. In the raw data, the date is formated as month/day. This is the way I want it on the chart. However, the X-axis formats with month/day/year. How can I force this to display correctly? Thanks, Mike. Do you see a grey little dropdown under the X axis (where you can choose your dates to show)? If yes, then rightclick on it. Select the top option: "Format pivotchart field" then click on the Number button on the next dialog. There are a bunch of date formats that you can choose from. Maybe...
Outlook Rules Query
Hi group... My email rules do not work if Outlook 2002 is set to send/receive automatically on startup. However... they do work if Outlook is set up to NOT send/receive on startup and I click send/receive shortly after starting Outlook 2002 However 2... they do work if the "Run Now" option is chosen so I do know that they actually do work. Is this normal... can someone suggest the correct settings to resolve this Thanks in advance Dave G ...
Query will not sort
I have made a query and it will not sort one of my fields. I thought it was a simple query but maybe not. Field: Area, Sort Ascending Field: OrderNumber, Sort Ascending Field: Status, Criteria Like "*Y*" Field: Week 1, Criteria Is Null It will do everything except sort on the order number. -- Lenee Lenee Please post the SQL statement of your query. ?Are you saying that the query should sort on OrderNumber first? If so, why is Area first? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services ...
Extender tables #2
Hello! We are implementing an integration from Microsoft CRM into Microsoft Great Plains and this would include writing CRM fields to Extender Fields. Are there any available SDK's for Extender or maybe any table lists? Thanks, Andi Saldana There are but I think only partners can get them. Please send me a message and I'll send you what I have. "Andi Saldana" wrote: > Hello! > > We are implementing an integration from Microsoft CRM into Microsoft Great > Plains and this would include writing CRM fields to Extender Fields. Are > there any avai...
Inserting table to other table
I have a form which consists of a table. I want to insert some selected value into other table when i update and at the same time I want it to save into my own table too... May I know the way please..... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200704/1 I'm having trouble visualizing your situation. Could you provide an example of data? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner http...
GP 9.0 Table descriptions
Is there any way to find the descriptions for each table in GP? Thanks, Tony Absolutely! Go to Tools >> Resource Descriptions >> Tables. Every table is listed with all the fields. You should also consult the SDK that is on the second GP cd. Additionally, Richard Whaley has published a series of books at Accolades Publishing that does a thorough job of documenting the tables, much better than what MS offers. -- Charles Allen, MVP "Tony" wrote: > Is there any way to find the descriptions for each table in GP? > > Thanks, > Tony > > &g...
Append query and error
Hi, I use the Append query to append information from the table A to table B. I got an error message "Microsoft Office Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 13041 records to the table due to key violations." However, it works- It added 13041 records to table B- but I don't know where the errors are and how to fix it. Would you please show me what I should to do ? Please help It sounds as if you are trying to add the 13041 records again. I think that you ran the append query and it put the 13041 records in table B, then yo...
Add a field to a Make table on the fly
I have a DB with 5 tbl that all have like keys but different data. I need to be able to search the 5 tbls and return to the user all the find condition and allow them to select the one they want by dblclick. I want to add a field on the fly as I build the search query to id what tbl the record came from is there a simple way? the field can have a constant for each of the different tbl like tbl1, tbl2, etc... -- Les ...
scheduling update query to run automatically?
Is it possible to have an update query run at a set time or interval? or to have it run when ever the database is shut down? You can youse VBScript to connect and execute the update then schedule it via TaskManager or some scheduling tool. "norm" <normkara.remove@nyc.rr.com> wrote in message news:1F47C08B-0858-4B8B-939E-E904068DF3D7@microsoft.com... > Is it possible to have an update query run at a set time or interval? or > to have it run when ever the database is shut down? On Wed, 30 Jan 2008 19:15:46 -0500, "norm" <normkara.remove@nyc.rr.com&g...
In Access, How do I hide records, but include them in the total
I'm running a report where I have work orders. They are grouped by appr and comp. I need to create a report that will show all the appr tickets, but give me a total count of appr and comp. I have the report generated the way I want, with total count, but how do i hide just the comp tickets. xeoimom wrote: >I'm running a report where I have work orders. They are grouped by appr and >comp. I need to create a report that will show all the appr tickets, but >give me a total count of appr and comp. I have the report generated the way >I want, with total count, but ...
Ensuring only one commission per product in Access Table
Good afternoon, Please can someone help me. In my Access Database I have a table called tblCommission, with the fields: Commission_ID;Client_account; Product_code; Commission; Exchange (£ or $) I need a method to ensure that each client can have a commission set for every product - but that a client cant have to commission's for one product: eg: I need a method to prevent this: Comm_ID Client_account Product_code Commission Exchange 1 000001 Coke 3 $ 2 000001 Coke ...
Table Expert
When a case is resolved, there is a dropdown to select the amount of "Billable Time" for the case. I have searched tables but cannot find where this data is stored. Is there anyone that know the schema where this data is kept? Bryon, When you resolve a case, the resolution screen fields are added into the activity table as a resolution type of activity. The Billable Time value appears in the TimeSpent field. (While building my auditing application, VAST, I've had to become much more of a CRM schema expert that I ever wanted to be - by the way, you can see these reso...
Recreating HR tables
I've been having some problems with HR. I've had to create some tables so the program would run. I simply copied the tables from another compagnie where is was saying I had missing tables. I'm wondering if there is a list of Tables for HR and Canadian Payroll or if it were possible to recreate these missing tables another way? I currently have this error as well: Could not find stored procedure 'Compagnie.dbo.hrCompDoesHrCompExist' ...
How do you create a table with a shaded header in Word 2007 or 201
Hi.. Can anybody tell me, how you create a table, and then shade the first row in it to something like gray, then set the grid lines to gray or something, and maybe set the alignment in it to centered... This is 2007 or 2010, not 2003 in which it was possible to do so. Thanks. Select the first row of the table and then right click with the mouse and select Borders and shading. For the alignment, select and right click and select Cell alignment -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting ba...
How to update data in a query
One part of my database is used to keep track of contacts within clients. I created a query using a table and a crosstab query and exported it to excel. I had staff in my office update the contact information. Now I want to update the query with the new information from the excel spreadsheet. The spreadsheet includes updates to existing data and new record. How can I do this? It does not look like an update or apend query are what I am looking for. Thanks Brian On Mon, 5 Apr 2010 09:45:04 -0700, AccessIlliterate <AccessIlliterate@discussions.microsoft.com> wrot...
Including MDA information in transaction edit reports
GP 8.0 I would like to include MDA entry with the AP transaction edit list so the input can be reviewed before releasing a batch for posting. Same thing applies to our invoicing. What is the best way to accomplish this task? I have coded this once for a customer. The only method that can work seamlessly is a Dexterity customisation which uses a trigger to add extra lines into the temporary table used for the report just prior to printing. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer - Great Plains Microsoft Dynamics Support - Asia Pacific Microsoft Dynamics (f...
How to create a field in a table whose value will be calculated
I could use some help in creating a field in a table the value of which would be the continuing balance of simple arithmetic operations such as: T(n) = T(n-1) A(n) - S(n) where T=Total, A=value in a field to be added, S=value in a field to be subtracted, and n=record number Does this make sense? Slipperyrock, If I understand you correctly you want a calculated field in a table... that would be a no can do BUT you can do it in a query. Now, you can simulate a table view using a query and a form. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been den...
Pivot table subtotals 100% #2
This is what I am aiming for but how can I get 100% in subtotals for first year and the increase %age for the second and subsequent year please 2002 2003 Apples excellent 15 22.72% 16 24.23% good 21 31.82% 25 37.88% poor 30 46.46% 39 60.39% 66 100% 80 122.5% Greens excellent 12 17.65% 37 54.42% good 27 39.70% 51 74.98% poor 29 42.65% 47 69.12% ...
Table info
In Vendor Maintenance window , there are multiple Address ID's. In which table i can find this information. Great Plains 7.5 Thanks in advance Deepwater, PM00300 holds all the address information for each address ID. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports http://www.flex-solutions.com/gpreports.html "Deepwater" <Deepwater@discussions.microsoft.com> wrote in message news:F9296B22-2B44-4D64-9E3C-DC5FE4B07028@microsoft.com... > In Vendor Maintenance window , there are multiple Address ID's. In which > table i can find this...
#includes
I'm looking over my files this evening, and I'm noticing that some of my #includes (of my own header files) are in my .h files, while some are in my ..cpp files. Sometimes I have used incomplete class declarations as well. I have tried to find some good reading to help me understand this whole thing, but haven't so far. Could I get a bit of advice on this? I don't know if this is related, but one of my source files won't give me any of those "smart tips" when the cursor passes over various objects, unless it is something that I have declared in that file itsel...
copy contents including colour
Hi In cells A1:B73 I have a list of contacts which are colour coded. What I want to be able to do is to be able to enter the name of 1 of the contacts in lets say G1 and it will search for that name in A1:B73 and automatically copy its colour code over. Many thanks for any help. Al Hi, I'm assuming that the colour codeing isn't a result of conditional formatting and it's simply a fill colour. To do this you need VB. Alt+F11 to open vb editor. Double click the worksheet that this data are on and paste the code in on the right. Close VB editor and type your name...
Deleting employees
We loaded employees into Great Plains with e-connect 8.0.2.1 using taCreateEmployee and taCreateEmployeeAddress. I need to wipe this data out and re-load. I know that with the new version of e-connect there is a taDeleteEmployee but for now can someone tell me the tables I need to truncate out in order to re-run the taCreateEmployee and taCreateEmployeeAddress? thanks -- AHIhelp AHIhelp: The tables are listed below - these are the ones handled by the Employee Delete procedure so I would assume these are the ones you would need to manually handle now as well UPR00100 UPR00102 UPR00...
Table Manners
In Outlook how can I implant a table in two or more colums, to obviate the necessity to scroll down if it is a lengthy table? ...
I've inharited a spreadsheet that I need to change the query to an external source...
I've inharited a spreadsheet that connects to an external source, our SQL 2005 server. I've altered the query to reflect our server name and saved it in Excel as a query with a new name. If I press F5 I can select the range, then I click on the Edit Query icon . First it says "This query cannot be edited by the Query Wizard", I press OK. Then it says "Invalid object name "TheirSERVERNAME.odb.SVSLSITM, Statement(s) could not be prepared", which makes sense since their server is different then ours. If I press OK there it takes me to basically a blank screen/...