If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Union Query
I currently have the following union query set up:
SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_COMMERCIAL] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_PHARMACIES] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_CHRYSLER] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_GE]; What I would like is it to total the number of records in each platform by date. Also, how would i get it so when the user clicks a button they are prompted for a date(s) so they get the information of how many entries per platform for a date or date range? |
#2
|
|||
|
|||
Union Query
Based on your query, you appear to have one table per company (and per
'type' - commercial..., and per industry - pharmacies). This design is not well-normalized. It looks rather like a spreadsheet (one sheet per ...). So what, you ask? So Access is a relational database and 'expects' well-normalized data. Both you and Access will have to work overtime to overcome the problems you'll have with trying to feed it 'sheet data. Yes, you can use a UNION query. Yes, you can use parameters (look in Access HELP for "parameter query"). (but you'll be saving yourself a lot of work later if you work on normalizing your data first...) Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "LG" wrote in message ... I currently have the following union query set up: SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_COMMERCIAL] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_PHARMACIES] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_CHRYSLER] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_GE]; What I would like is it to total the number of records in each platform by date. Also, how would i get it so when the user clicks a button they are prompted for a date(s) so they get the information of how many entries per platform for a date or date range? |
#3
|
|||
|
|||
Union Query
I understand that it would be easier to put all into table the problem lies
is there are many users in it entering data into each table daily. There is also a team of people to do corrections therefore easier for each form to be set up to a seperate table for correction purposes. Some fields in the tables are slightly different but are identified as a batch id that a processor uses. Do you have any suggestions where if I combine them how would I make a form for corrections where they are not looking through too many records. Also, annually there will be over million entries with all the tables. Any suggestions "Jeff Boyce" wrote: Based on your query, you appear to have one table per company (and per 'type' - commercial..., and per industry - pharmacies). This design is not well-normalized. It looks rather like a spreadsheet (one sheet per ...). So what, you ask? So Access is a relational database and 'expects' well-normalized data. Both you and Access will have to work overtime to overcome the problems you'll have with trying to feed it 'sheet data. Yes, you can use a UNION query. Yes, you can use parameters (look in Access HELP for "parameter query"). (but you'll be saving yourself a lot of work later if you work on normalizing your data first...) Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "LG" wrote in message ... I currently have the following union query set up: SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_COMMERCIAL] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_PHARMACIES] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_CHRYSLER] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_GE]; What I would like is it to total the number of records in each platform by date. Also, how would i get it so when the user clicks a button they are prompted for a date(s) so they get the information of how many entries per platform for a date or date range? |
#4
|
|||
|
|||
Union Query
To count all within a date range for which the user is prompted, returning
all rows if the parameters values are not entered : PARAMETERS [Enter start date:] DATETIME, [Enter end date:] DATETIME; SELECT [date], [platform], COUNT(*) AS Total FROM (SELECT [date], [platform] FROM [tbl_commercial] UNION ALL SELECT [date], [platform] FROM [tbl_pharmacies] UNION ALL SELECT [date], [platform] FROM [tbl_chrysler]) WHERE ([date] = [Enter start date:] OR [Enter start date:] IS NULL) AND ([date] DATEADD("d",1",[Enter end date:]) OR [Enter end date:] IS NULL) GROUP BY [date], [platform]; Note that the references to the column names in the outer query must be to the names of the columns in the first part of the UNION ALL operations in the subquery. In this case all three use the same column names of course. Also note that the parenthesising of the OR operations in the WHERE clause is crucial to force them to evaluate independently of the AND operations. You can of course reverse the group order to [platform],[date] if you prefer. NB: Date is a bad choice for a column name as it’s the name of a built in function. Wrapping it in square brackets as above should avoid any misinterpretation of it by Access, but its best to use a more specifically descriptive term when naming columns, e.g. TransactionDate. Ken Sheridan Stafford, England LG wrote: I currently have the following union query set up: SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_COMMERCIAL] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_PHARMACIES] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_CHRYSLER] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_GE]; What I would like is it to total the number of records in each platform by date. Also, how would i get it so when the user clicks a button they are prompted for a date(s) so they get the information of how many entries per platform for a date or date range? -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Union Query
The messge I get is invalid SQL statment;expected 'DELETE', 'INSERT',
'PROCEDURE', 'SE:ECT', or 'UPDATE'. Would you also suggest putting these tables together and matching up all fields? "KenSheridan via AccessMonster.com" wrote: To count all within a date range for which the user is prompted, returning all rows if the parameters values are not entered : PARAMETERS [Enter start date:] DATETIME, [Enter end date:] DATETIME; SELECT [date], [platform], COUNT(*) AS Total FROM (SELECT [date], [platform] FROM [tbl_commercial] UNION ALL SELECT [date], [platform] FROM [tbl_pharmacies] UNION ALL SELECT [date], [platform] FROM [tbl_chrysler]) WHERE ([date] = [Enter start date:] OR [Enter start date:] IS NULL) AND ([date] DATEADD("d",1",[Enter end date:]) OR [Enter end date:] IS NULL) GROUP BY [date], [platform]; Note that the references to the column names in the outer query must be to the names of the columns in the first part of the UNION ALL operations in the subquery. In this case all three use the same column names of course. Also note that the parenthesising of the OR operations in the WHERE clause is crucial to force them to evaluate independently of the AND operations. You can of course reverse the group order to [platform],[date] if you prefer. NB: Date is a bad choice for a column name as it’s the name of a built in function. Wrapping it in square brackets as above should avoid any misinterpretation of it by Access, but its best to use a more specifically descriptive term when naming columns, e.g. TransactionDate. Ken Sheridan Stafford, England LG wrote: I currently have the following union query set up: SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_COMMERCIAL] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_PHARMACIES] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_CHRYSLER] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_GE]; What I would like is it to total the number of records in each platform by date. Also, how would i get it so when the user clicks a button they are prompted for a date(s) so they get the information of how many entries per platform for a date or date range? -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
Union Query
You've raised two issues he
1. That of restricting each corrector's form to the relevant 'industry type' when the data is combined in a single table, which is easily done by basing the form on a query which restricts the results to the 'industry type' in question. This can be done by a parameter in the query, e.g. WHERE IndustryType = [Enter industry type:] but would be by done by opening the form from a dialogue for with a combo or list box of industry types which the bound form's query references as the parameter, e.g. WHERE IndustryType = Forms!frmIndustryDialog!cboIndustry Your tables would appear to represent some sort of transactions, with each table being restricted to those for a particular 'industry type'. So if within each 'industry type' there are many different companies (or whatever) of each 'industry type' then not only should these tables be combined into one there should also be a combined table of companies which includes an IndustryType column. Your combined Transactions (or whatever) table would include a CompanyID column as a foreign key which references the primary key of the Companies table. The Transactions table would not have an IndustryType column (that would introduce redundancy) but the query on which you corrector's form is based would join the Transactions and Companies table on the CompanyID columns so that the query could be restricted on the InsustryType column from Companies. However, the process of normalization doesn't stop there as you also need a table with one row per industry type which your combined Companies table can then be related to on the IndustryType column, enforcing referential integrity and cascade updates. This protects the integrity of the data, but also provides a source for the combo box on the dialogue form, whose RowSource can then be; SELECT IndustryType FRO IndustryTypes ORDER BY IndustryType; 2. If when you say "some fields in the tables are slightly different" you mean one table might represent an entity type which has different attributes to the entity types then what you have is a Type/Sub-types scenario. The Type represents all industry types, so is represented by your combined table. The columns of this table would represent the attributes which all 'industry types' share in common, including your platform and date attributes by the look of things. The Sub-types would be represented by a set of tables, each with columns which represent the differing attributes of each individual 'industry type' which it does not share with all others. These sub-type tables would have a primary key which is also a foreign key referencing the primary key of the (super) IndustryTypes table, i.e. the relationship is one- to-one. So putting all of the above together the model would look like this: Transactions---Companies---IndustryTypes---MultipleIndustrySubTypeTables You'll see from this that by the process of 'decomposition' into a number of related tables has eliminated any redundancies, as each 'fact' is now stored in one place only, and is stored as a value at a column position in a row in a table. It’s a fundamental principle of the database relational model that data is stored in that way and no other way, known as the 'information principle'. Its how the integrity of data is protected. The entity types which I've assumed as examples probably don't tally completely with your real world entity types, but the principles will be the same. Ken Sheridan Stafford, England LG wrote: I understand that it would be easier to put all into table the problem lies is there are many users in it entering data into each table daily. There is also a team of people to do corrections therefore easier for each form to be set up to a seperate table for correction purposes. Some fields in the tables are slightly different but are identified as a batch id that a processor uses. Do you have any suggestions where if I combine them how would I make a form for corrections where they are not looking through too many records. Also, annually there will be over million entries with all the tables. Any suggestions Based on your query, you appear to have one table per company (and per 'type' - commercial..., and per industry - pharmacies). This design is not [quoted text clipped - 35 lines] for a date(s) so they get the information of how many entries per platform for a date or date range? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200907/1 |
#7
|
|||
|
|||
Union Query
It sounds like you've made some error in entering the SQL statement; a query
of the type whose SQL I posted should work. It helps when you get this sort of error if you copy the miscreant SQL statement into your post. As regards combining the tables, the answer is broadly speaking 'Yes', but see my other reply for a more detailed analysis. Ken Sheridan Stafford, England LG wrote: The messge I get is invalid SQL statment;expected 'DELETE', 'INSERT', 'PROCEDURE', 'SE:ECT', or 'UPDATE'. Would you also suggest putting these tables together and matching up all fields? To count all within a date range for which the user is prompted, returning all rows if the parameters values are not entered : [quoted text clipped - 52 lines] for a date(s) so they get the information of how many entries per platform for a date or date range? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200907/1 |
#8
|
|||
|
|||
Union Query
You can use the saved UNION query as if it were a table.
SELECT Platform, [Date], Count(Batch_ID) as RecordCount FROM qUnion GROUP BY Platform, [Date] If you wish to specify a date range then Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime; SELECT Platform, [Date], Count(Batch_ID) as RecordCount FROM qUnion WHERE [Date] Between [Enter Start Date] and [Enter End Date] GROUP BY Platform, [Date] If you only know how to use the query design tool (query grid) -- Open a new query -- Add the union query to the new query (instead of a table) -- add fields Platform, Date, and Batch_id to the fields to display -- Select View: Totals from the menu -- Change GROUP BY to COUNT under Batch_ID To add the where -- Add Date a second time to the field list -- Change GROUP BY to WHERE under this additional field -- Type the following into the criteria Between [Enter Start Date] and [Enter End Date] John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County LG wrote: I currently have the following union query set up: SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_COMMERCIAL] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_PHARMACIES] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_CHRYSLER] UNION SELECT [BATCH_ID], [DATE], [PLATFORM] FROM [TBL_GE]; What I would like is it to total the number of records in each platform by date. Also, how would i get it so when the user clicks a button they are prompted for a date(s) so they get the information of how many entries per platform for a date or date range? |
Thread Tools | |
Display Modes | |
|
|