Data entry Query
Hi I am trying to have a query open up (which later I will use as a form) so that I can do data entry, but because I am joining a table it does not allow me to add records (One to many relationship). The PartNumberTests_tbl table contains all tests pertaining to a specific part number. In the TestResults_tbl table I want to be able to add the results of each test that applies to the queried partnumber. I do not need to write anything to the PartNumberTests_tbl table since it is updated by the use of a form (adding or modifying test parameters for a specific part number) Can this be done? should I be using a different approach? Here is my query. SELECT PartNumberTests_tbl.PartNumberID, TestResults_tbl.PartNumberID, TestResults_tbl.TestID, TestResults_tbl.Min, TestResults_tbl.Max, TestResults_tbl.Result, TestResults_tbl.PassFailResult FROM PartNumberTests_tbl LEFT JOIN TestResults_tbl ON PartNumberTests_tbl.PartNumberID = TestResults_tbl.PartNumberID WHERE (((PartNumberTests_tbl.PartNumberID)=1));
|
0 |
|
Angel
9/20/2007 9:05:11 PM
access.queries
6343 articles. 
1 followers.
1 Replies
777 Views
I would suggest a form/subform with Master/Child links using PartNumberID. But you will not get many records with WHERE (((PartNumberTests_tbl.PartNumberID)=1)) -- KARL DEWEY Build a little - Test a little "Angel G" wrote: > Hi I am trying to have a query open up (which later I will use as a form) so > that I can do data entry, but because I am joining a table it does not allow > me to add records (One to many relationship). The PartNumberTests_tbl table > contains all tests pertaining to a specific part number. In the > TestResults_tbl table I want to be able to add the results of each test that > applies to the queried partnumber. I do not need to write anything to the > PartNumberTests_tbl table since it is updated by the use of a form (adding > or modifying test parameters for a specific part number) > Can this be done? should I be using a different approach? > Here is my query. > > SELECT PartNumberTests_tbl.PartNumberID, TestResults_tbl.PartNumberID, > TestResults_tbl.TestID, TestResults_tbl.Min, TestResults_tbl.Max, > TestResults_tbl.Result, TestResults_tbl.PassFailResult > FROM PartNumberTests_tbl LEFT JOIN TestResults_tbl ON > PartNumberTests_tbl.PartNumberID = TestResults_tbl.PartNumberID > WHERE (((PartNumberTests_tbl.PartNumberID)=1)); > > >
|
|
0 |
|
Utf
9/20/2007 9:42:01 PM
Similar Artilces:
Combining Data #5
I have a question I am hoping someone can help me with. I have 2 worksheets that I need to merge the data. Both have a common field that I key on, Case ID. If the Case ID is found in both worksheets, the data will be pulled from worksheet 2. If the Case ID is exists in worksheet 1 but not 2, the data is pulled from worksheet 1. If the Case ID exists in worksheet 2 but not 1, the data is pulled from worksheet 2. Basically, I am exporting data from a system that only gives me current data. That file may contain existing cases or new cases that the old file does not. The old file ma...
Function to mapping data
Hi all, I am struggling for an excel data combination. Hope someone can help me. Below col A and Col B is a pair while col C and col D is another pair, originally col E, F and G are empty. Then in col E, col F , col G, they copy Ax+Cx(eliminate duplicate), Bx, Dx respectively. A B C D E F G C1 AA C1 111 C1 AA 111 C2 BB C3 222 C2 BB C3 CC D1 333 C3 CC 222 D1 DD D2 444 D1 DD 333 D3 EE D3 523 D2 444 D4 SS D6 321 D3 EE 523 E4 345 D4 SS D6 321 E4 345 From the above sample, col A & col C are copied to E1(eliminate duplicate) and then col Bx copied to Fx(e..g c...
IE or Excel caching data?
I am accessing a website that generates csv reports for many different facilities I service. The csv files all have the same name and, subsequently, the same URL. There is some kind of backend script that generates these files and opens them in Excel for me automatically when I click the appropriate link. The first time I click the link, it works fine. The second time, I click the link that is associated with a different facility, but the data for the first facility still shows up in the csv Excel file. I have played with IE's cache, cookies, history, and advanced settings to no av...
Office 2007
I have been working with Office 2007 beta. In prior versions I was able to use the mouse to highlight the cells that were to be added to a chart. I cannot highlight the cells that I need, but have to manually type in the beginning and ending cell range. ...
How do I link data to a chart from multiple worksheets?
I've created a graph that I want to show data from multiple sheets in the workbook. How do I do that? There are limits to how you can combine data from different sheets. This article describes them. http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Donna" <Donna@discussions.microsoft.com> wrote in message news:7DF7D186-9426-4B54-A322-FE485FD20529@microsoft.com... > I've created a graph that I want to...
Data Validation
Is it possible to get list of unique values for data validation. In range a2 - a10 that I use as data validation list i have values x, y, z, x, x, ... In a drop down I get letter x listed more than one time. Is it possible to make it unique? I use Excel 2003. Thanks. Hi Oscar Use a different source as your DV list that only has one of each entry. If your list is as short as you say, then manually typing it would be the easiest option. If, in fact it is a much bigger list in reality, then use Advanced filter Select your source range>Data>Advanced Filter>Unique valu...
How can I make another version without all the data
Hello all, I have build a report in excel. The workbook (nr. 1) contains multiple tabs which get their data from a txt-file. One of these tabs gets its data from another workbook (nr. 2), which is stored in the same folder / map. When I open the first workbook (nr. 1) it doesn't show data which is stored in the other workbook (nr. 2), but when I open both files at the same time the problem is solved. But now comes the real problem. One of the tabs of workbook nr. 1 is saved as a pdf-file. This tabs contains graph's and a summary of the data in the other tabs. Is there a possibility to...
MS Query No visible tables
I am trying to link Excel to an Access file using MS Query (Data/Get External Data/New database Query/MS Access DAtabase. When I select the file, I get the message "This data source contains no visible tables". But the Access file has 5 tables. I have tried other Access files, some of which work OK, and others not. What do I need to do to the Access file to make this work? Hi Chris, The tables are probably hidden. If Access is set up to show hidden tables, you may not notice this, although the icon is slightly dimmer. Open the database, right click on the table name, select ...
Copying calender entries via code
Hi Is there a way to copy entries from a calendar in the public folders to the standard calendar via code ? A code example would be highly appreciated. If this code can run under a timer then that would be even great. Many Thanks Regards Try this macro, which will copy all of the selected items in the active Calendar to the default Calendar: Sub CopyAppointmentItem() Dim objNS As Outlook.NameSpace Dim objAppt As Outlook.AppointmentItem Dim objCopiedAppt As Outlook.AppointmentItem If ActiveExplorer.Selection.Count = 0 Then Exit Sub Set objNS = Application...
Using a Form to Archive Old Data
Hello all: I have a table that contains some data, including a EmpDate field. I have a query that will bring up the last 90 days worth of data, based on that date field. What I need is to be able to bring up that 90 day history, click on a record, and hit a button that will move that record from the live table to an archive table, and append an "archived date stamp" to the record in the new table. I can provide more data or information if necessary, but I don't even know where to start with this one. I am assuming I will need to use some sort of subform? Any help is appreci...
Data Validation visibility
Data Validation Question I am building a spreadsheet using data validation but I would like th drop down boxes to stay visible at all times. I have researche thoroughly and am not aware of any way to have the icon remain visibl unless I switch to using combo boxes. I would assume this may be don via VBA but am not clear on the code. Thanks for the help. Chri -- Message posted from http://www.ExcelForum.com Hi AFAIK not possible -- Regards Frank Kabel Frankfurt, Germany > Data Validation Question > > I am building a spreadsheet using data validation but I would like the >...
Data Analysis in PWA 2007
Where can I find an exhaustive treatment of the details of Data Analysis in PWA 2007? jlbreyer -- A lot depends by what you mean by "exhaustive." Our Implementing and Administering Microsoft Office Project 2007 book has a thorough chapter in it about creating Data Analysis views and the OLAP cubes upon which they can be based. We also provide some real world examples of useful Data Analysis views that can be created for each family of OLAP cubes. Beyond this, the book also includes a free download file that includes two Excel workbooks that help the Project Serv...
Compare/Validate and add new data
Friends, I am new to VBA. I need a help in the following : I have 2 spreadsheets : A) Workbook A : C1, C2, C3, C4, C5 R1 R2 R3 R4 R5 B) Workbook B C1, C3, C5 R1 R2 R3 Note the following : 1. Workbook A has more columns and rows (new rows). 2. Both workbooks will have data My Requirement: I want to update Workbook B with following requirements: 1) Look for all existing rows and columns of Workbook B in Workbook A and then update Workbook B with latest values for those already existing and then add new rows with only required columns (C1, C3 & C5) of new rows. 2) I also ...
'xxxxx' data in SOP, POP files
GP Version 9, running on SQL 2000 w/Nolan Advanced SOP, Myridas Intersite, Myridas Vendor Pricing, GP Manufacturing We have 10 GP companies. Last week, 2 of the companies had a couple of Sales Order Headers and Purchase Order headers where all of the character data was 'xxxx' and all of the numeric data was '999. Some of the orders had detail lines with the same kind of data. The SOP headers had SOP_TYPE = 7 and 8. The data looks suspiciously like the data used for alignment or what appears when you do a print preview in GP report writer. Can anyone shed light on this even...
pass through query
If a combobox is populated by a pass-through query, does it requery every time the form is closed and reopened? Thanks, Sam Yes. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Sam wrote: > If a combobox is populated by a pass-through query, does it requery every > time the form is closed and reopened? > Thanks, > Sam ...
validation rule in query?
I have a validation rule in the table to specifically enter one of 8 state abbreviations. ="IA" Or " IL" Or "IN" Or " MI" Or "MN" Or "MO" Or "OH" Or "WI" The Validation text that appears if not followed is Not a valid R3 State Abbreviation I have built several queries based on this table and have a parameter query for State that says Enter State Abbreviation. However, if one mis-types the state, the validation text doesn't appear. Is there something special I need to do?? Thanks, Linda Hi - The v...
Adding a Formula as a data variable in a pivot
Is it possible to create a formula as data column in a pivot table? For eamaple let's say I have the following Table: Name Billed Charges Paid Charges George 1000 500 Pilar 2000 750 Fred 1211 602 I would like to add a data variable of [Billed Charges] - [Paid Charges] and another [Billed Charges] / [Paid Charges] -1 I know that one way to do this is to add columns to the raw data and refresh the pivot, but I am wondering whether I can create a calculated formula as...
How to query
I have 2 fields in a table JobNo Employee 1 A 1 B 2 X 2 Y 3 H 3 J 3 K and want to query result JobNo Employee1 Employee2 Employee3 1 A B 2 X Y 3 H J K How to create query? hi Nova, On 24.03.2010 09:51, Nova wrote: > I have 2 fields in a table > JobNo Employee > 1 A > and want to query result > JobNo Employee1 ...
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...
VLookup/Data Validation
Last week I posted for help w/VLookUp, Data Validation or anything that would populate a spreadsheet based on a criteria. I don't think I explained correctly or gave enough information, so I'm desperately trying this again - I need so much help. I used http://www.contextures.com/excelfiles.html#DataVal - ProductsList and did some editing to suit our needs. I have a spreadsheet with 2 tabs. Tab 2 is called EmployeeList, with a list our employees with their name, start date, dept, roll (manager, clerk, etc.) and a list of classes (Class I, Class II, Class II) needed to attend (all in...
Autonumbering Query
I have 2 tables. Table 1 contains an indexed autonumber field named QID. Table 2 contains the same name field - it is a number field. The two fields are linked in the relationships as 1 to many and the form is working as expected. What I am trying to do is insert a secondary autonumber field in Table 2 which will restart at #1 everytime a new record is created in Table 1. Eg. Table 1 will have record 1, 2, 3....etc. I want Table 2 to have record 1.1, 1.2, 1.3....etc. (the 1 coming from the QID field in Table 1). The .1 etc. from the autonumber field in Table 2. Therefore ...
Distilling a Query?
I have two tables: one that lists Clients and another that lists Client Projects. The tables have a relationship between their respective Client fields. Not every Client in the Client Table has a Project in the Client Projects Table. Furthermore, many of the Clients who do have a Project have several Projects in the Client Projects Table. I have managed to create a query that lists only Clients who have a Project in the Client Projects Table but each Client is listed as many times as it has Projects. My question is: how can I create a Query that lists all the Clients who have a Proje...
Capturing Query Parameters
I've got a report I've built for a customer that runs a query that has 2 parameters which requires the user to input a start date and an end date. The query runs correctly based on the user's date selections. How do I capture the dates selected as parameters and include them in the overall report? This way the user knows which dates they selected for the results they see. I'd like them displayed in the report header for example. Thanks in advance for any suggestions. Steve The parameter names are available in the report. For example, if you called the first paramet...
GL Trial balance via sql query
Hi everybody - I need to be able to create a trial balance via a sql query. I know GP already has this report built in, but I need to make it via a query. Your help is appreciated. Hi Michael, You can start creating your query with the following company tables, which will provide you the following: GL00100 - Account Master GL20000 - Open Year Transaction Details GL30000 - Historical Year transaction Details GL00105 - Account number string, an alternative from conbining account segments in GL00100 The following script is a very simple SQL query that is functionally TB ...
Data in my pivot table changes to zeros
I have created a pivot table using a dynamic named range as my source. The source data consists of 52,416 rows and 10 columns showing: Membership code Date Hour Day Week Month Term Year Checkouts Checkins Everything appears as expected when I go through Excel's PivotTabl wizard but the -Sum of Checkins- and -Sum of Checkouts- (which I plac in the data area of the PT) always come up as zeros. The PT seems t have replaced all the correct data in each cell with a zero! In addition, I have to manually change the fields to "-Sum of-..." a opposed to the "-Count of-..." ...
I have a question I am hoping someone can help me with. I have 2 worksheets that I need to merge the data. Both have a common field that I key on, Case ID. If the Case ID is found in both worksheets, the data will be pulled from worksheet 2. If the Case ID is exists in worksheet 1 but not 2, the data is pulled from worksheet 1. If the Case ID exists in worksheet 2 but not 1, the data is pulled from worksheet 2. Basically, I am exporting data from a system that only gives me current data. That file may contain existing cases or new cases that the old file does not. The old file ma...
Function to mapping data
Hi all, I am struggling for an excel data combination. Hope someone can help me. Below col A and Col B is a pair while col C and col D is another pair, originally col E, F and G are empty. Then in col E, col F , col G, they copy Ax+Cx(eliminate duplicate), Bx, Dx respectively. A B C D E F G C1 AA C1 111 C1 AA 111 C2 BB C3 222 C2 BB C3 CC D1 333 C3 CC 222 D1 DD D2 444 D1 DD 333 D3 EE D3 523 D2 444 D4 SS D6 321 D3 EE 523 E4 345 D4 SS D6 321 E4 345 From the above sample, col A & col C are copied to E1(eliminate duplicate) and then col Bx copied to Fx(e..g c...
IE or Excel caching data?
I am accessing a website that generates csv reports for many different facilities I service. The csv files all have the same name and, subsequently, the same URL. There is some kind of backend script that generates these files and opens them in Excel for me automatically when I click the appropriate link. The first time I click the link, it works fine. The second time, I click the link that is associated with a different facility, but the data for the first facility still shows up in the csv Excel file. I have played with IE's cache, cookies, history, and advanced settings to no av...
Office 2007
I have been working with Office 2007 beta. In prior versions I was able to use the mouse to highlight the cells that were to be added to a chart. I cannot highlight the cells that I need, but have to manually type in the beginning and ending cell range. ...
How do I link data to a chart from multiple worksheets?
I've created a graph that I want to show data from multiple sheets in the workbook. How do I do that? There are limits to how you can combine data from different sheets. This article describes them. http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Donna" <Donna@discussions.microsoft.com> wrote in message news:7DF7D186-9426-4B54-A322-FE485FD20529@microsoft.com... > I've created a graph that I want to...
Data Validation
Is it possible to get list of unique values for data validation. In range a2 - a10 that I use as data validation list i have values x, y, z, x, x, ... In a drop down I get letter x listed more than one time. Is it possible to make it unique? I use Excel 2003. Thanks. Hi Oscar Use a different source as your DV list that only has one of each entry. If your list is as short as you say, then manually typing it would be the easiest option. If, in fact it is a much bigger list in reality, then use Advanced filter Select your source range>Data>Advanced Filter>Unique valu...
How can I make another version without all the data
Hello all, I have build a report in excel. The workbook (nr. 1) contains multiple tabs which get their data from a txt-file. One of these tabs gets its data from another workbook (nr. 2), which is stored in the same folder / map. When I open the first workbook (nr. 1) it doesn't show data which is stored in the other workbook (nr. 2), but when I open both files at the same time the problem is solved. But now comes the real problem. One of the tabs of workbook nr. 1 is saved as a pdf-file. This tabs contains graph's and a summary of the data in the other tabs. Is there a possibility to...
MS Query No visible tables
I am trying to link Excel to an Access file using MS Query (Data/Get External Data/New database Query/MS Access DAtabase. When I select the file, I get the message "This data source contains no visible tables". But the Access file has 5 tables. I have tried other Access files, some of which work OK, and others not. What do I need to do to the Access file to make this work? Hi Chris, The tables are probably hidden. If Access is set up to show hidden tables, you may not notice this, although the icon is slightly dimmer. Open the database, right click on the table name, select ...
Copying calender entries via code
Hi Is there a way to copy entries from a calendar in the public folders to the standard calendar via code ? A code example would be highly appreciated. If this code can run under a timer then that would be even great. Many Thanks Regards Try this macro, which will copy all of the selected items in the active Calendar to the default Calendar: Sub CopyAppointmentItem() Dim objNS As Outlook.NameSpace Dim objAppt As Outlook.AppointmentItem Dim objCopiedAppt As Outlook.AppointmentItem If ActiveExplorer.Selection.Count = 0 Then Exit Sub Set objNS = Application...
Using a Form to Archive Old Data
Hello all: I have a table that contains some data, including a EmpDate field. I have a query that will bring up the last 90 days worth of data, based on that date field. What I need is to be able to bring up that 90 day history, click on a record, and hit a button that will move that record from the live table to an archive table, and append an "archived date stamp" to the record in the new table. I can provide more data or information if necessary, but I don't even know where to start with this one. I am assuming I will need to use some sort of subform? Any help is appreci...
Data Validation visibility
Data Validation Question I am building a spreadsheet using data validation but I would like th drop down boxes to stay visible at all times. I have researche thoroughly and am not aware of any way to have the icon remain visibl unless I switch to using combo boxes. I would assume this may be don via VBA but am not clear on the code. Thanks for the help. Chri -- Message posted from http://www.ExcelForum.com Hi AFAIK not possible -- Regards Frank Kabel Frankfurt, Germany > Data Validation Question > > I am building a spreadsheet using data validation but I would like the >...
Data Analysis in PWA 2007
Where can I find an exhaustive treatment of the details of Data Analysis in PWA 2007? jlbreyer -- A lot depends by what you mean by "exhaustive." Our Implementing and Administering Microsoft Office Project 2007 book has a thorough chapter in it about creating Data Analysis views and the OLAP cubes upon which they can be based. We also provide some real world examples of useful Data Analysis views that can be created for each family of OLAP cubes. Beyond this, the book also includes a free download file that includes two Excel workbooks that help the Project Serv...
Compare/Validate and add new data
Friends, I am new to VBA. I need a help in the following : I have 2 spreadsheets : A) Workbook A : C1, C2, C3, C4, C5 R1 R2 R3 R4 R5 B) Workbook B C1, C3, C5 R1 R2 R3 Note the following : 1. Workbook A has more columns and rows (new rows). 2. Both workbooks will have data My Requirement: I want to update Workbook B with following requirements: 1) Look for all existing rows and columns of Workbook B in Workbook A and then update Workbook B with latest values for those already existing and then add new rows with only required columns (C1, C3 & C5) of new rows. 2) I also ...
'xxxxx' data in SOP, POP files
GP Version 9, running on SQL 2000 w/Nolan Advanced SOP, Myridas Intersite, Myridas Vendor Pricing, GP Manufacturing We have 10 GP companies. Last week, 2 of the companies had a couple of Sales Order Headers and Purchase Order headers where all of the character data was 'xxxx' and all of the numeric data was '999. Some of the orders had detail lines with the same kind of data. The SOP headers had SOP_TYPE = 7 and 8. The data looks suspiciously like the data used for alignment or what appears when you do a print preview in GP report writer. Can anyone shed light on this even...
pass through query
If a combobox is populated by a pass-through query, does it requery every time the form is closed and reopened? Thanks, Sam Yes. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Sam wrote: > If a combobox is populated by a pass-through query, does it requery every > time the form is closed and reopened? > Thanks, > Sam ...
validation rule in query?
I have a validation rule in the table to specifically enter one of 8 state abbreviations. ="IA" Or " IL" Or "IN" Or " MI" Or "MN" Or "MO" Or "OH" Or "WI" The Validation text that appears if not followed is Not a valid R3 State Abbreviation I have built several queries based on this table and have a parameter query for State that says Enter State Abbreviation. However, if one mis-types the state, the validation text doesn't appear. Is there something special I need to do?? Thanks, Linda Hi - The v...
Adding a Formula as a data variable in a pivot
Is it possible to create a formula as data column in a pivot table? For eamaple let's say I have the following Table: Name Billed Charges Paid Charges George 1000 500 Pilar 2000 750 Fred 1211 602 I would like to add a data variable of [Billed Charges] - [Paid Charges] and another [Billed Charges] / [Paid Charges] -1 I know that one way to do this is to add columns to the raw data and refresh the pivot, but I am wondering whether I can create a calculated formula as...
How to query
I have 2 fields in a table JobNo Employee 1 A 1 B 2 X 2 Y 3 H 3 J 3 K and want to query result JobNo Employee1 Employee2 Employee3 1 A B 2 X Y 3 H J K How to create query? hi Nova, On 24.03.2010 09:51, Nova wrote: > I have 2 fields in a table > JobNo Employee > 1 A > and want to query result > JobNo Employee1 ...
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...
VLookup/Data Validation
Last week I posted for help w/VLookUp, Data Validation or anything that would populate a spreadsheet based on a criteria. I don't think I explained correctly or gave enough information, so I'm desperately trying this again - I need so much help. I used http://www.contextures.com/excelfiles.html#DataVal - ProductsList and did some editing to suit our needs. I have a spreadsheet with 2 tabs. Tab 2 is called EmployeeList, with a list our employees with their name, start date, dept, roll (manager, clerk, etc.) and a list of classes (Class I, Class II, Class II) needed to attend (all in...
Autonumbering Query
I have 2 tables. Table 1 contains an indexed autonumber field named QID. Table 2 contains the same name field - it is a number field. The two fields are linked in the relationships as 1 to many and the form is working as expected. What I am trying to do is insert a secondary autonumber field in Table 2 which will restart at #1 everytime a new record is created in Table 1. Eg. Table 1 will have record 1, 2, 3....etc. I want Table 2 to have record 1.1, 1.2, 1.3....etc. (the 1 coming from the QID field in Table 1). The .1 etc. from the autonumber field in Table 2. Therefore ...
Distilling a Query?
I have two tables: one that lists Clients and another that lists Client Projects. The tables have a relationship between their respective Client fields. Not every Client in the Client Table has a Project in the Client Projects Table. Furthermore, many of the Clients who do have a Project have several Projects in the Client Projects Table. I have managed to create a query that lists only Clients who have a Project in the Client Projects Table but each Client is listed as many times as it has Projects. My question is: how can I create a Query that lists all the Clients who have a Proje...
Capturing Query Parameters
I've got a report I've built for a customer that runs a query that has 2 parameters which requires the user to input a start date and an end date. The query runs correctly based on the user's date selections. How do I capture the dates selected as parameters and include them in the overall report? This way the user knows which dates they selected for the results they see. I'd like them displayed in the report header for example. Thanks in advance for any suggestions. Steve The parameter names are available in the report. For example, if you called the first paramet...
GL Trial balance via sql query
Hi everybody - I need to be able to create a trial balance via a sql query. I know GP already has this report built in, but I need to make it via a query. Your help is appreciated. Hi Michael, You can start creating your query with the following company tables, which will provide you the following: GL00100 - Account Master GL20000 - Open Year Transaction Details GL30000 - Historical Year transaction Details GL00105 - Account number string, an alternative from conbining account segments in GL00100 The following script is a very simple SQL query that is functionally TB ...
Data in my pivot table changes to zeros
I have created a pivot table using a dynamic named range as my source. The source data consists of 52,416 rows and 10 columns showing: Membership code Date Hour Day Week Month Term Year Checkouts Checkins Everything appears as expected when I go through Excel's PivotTabl wizard but the -Sum of Checkins- and -Sum of Checkouts- (which I plac in the data area of the PT) always come up as zeros. The PT seems t have replaced all the correct data in each cell with a zero! In addition, I have to manually change the fields to "-Sum of-..." a opposed to the "-Count of-..." ...