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
|
|||
|
|||
Display of information
Hi
I've set up a database with sales figures. I have a table (called Data) which has Date, Group, Sales, Budget, YTD and this holds the raw data which I need to display. I have created three queries using this info which is based on the date entered in a form. One query has 12 records (one for each Group) and each record has Sales, GP and Budget figures. The second query has another 12 records, the same as the first query, but for the same month last year. The third query has up to 144 records (depending on the month) as this selects the YTD figures. I now need to display this information. I thought about using a form - but I don't know how to draw the information from three different queries. I then set up a table, so I could push the info into that - with a single append query and a load of update queries, but I don't know how to set up the update queries to use the other three queries I've already written. I'm sure there must be an easy/easier way to do this. Am I coming at it from the wrong direction? Any help/advice/experience would be very much appreciated. Cheers. -- Andy. |
#2
|
|||
|
|||
Hi, Andy.
Before I can advise you, please describe, or better still, diagram, what you’d like the user to see, and explain the generic purpose of your application in plain language. I suspect you’re table is not normalized, which would making life more complicated, but I’ll reserve judgment until I understand what you’re trying to do. I can say the following, however: • If you’re finding you need to run a number of insert queries just in order to display the data you’re after, there’s likely a much simpler approach. • Normally, YTD numbers would never be stored in a table, they would be calculated on-the-fly whenever they’re needed in a calculated field in a query. • ‘Date’ is an Access-reserved word. Naming fields with reserved words can cause unpredictable behavior. Use something like ‘SalesDate’ instead. A Google search can give you a complete list of Access reserved words. • You don’t mention a primary key. Virtually all Access tables should have a primary key as a unique record identifier. This can be a single field or multiple ones, although most developers I know use a simple AutoNumber key. If they want to prevent duplicate records of say, a combination of Group and Month, they add a No Duplicates index in table design view. • Although I suspect your solution will be simpler than you’ve described, one displays information from multiple tables or queries by joining them in a query by their common field, selecting the fields of interest, and basing the form on the query. For example, to display the customer name & address info plus the information about one of their orders on a form, you need information from the Customers and Orders tables. The query would join the two tables by the common field CustomerID, which is the primary key of Customers and the foreign key in Orders. You can do the same with queries. Post the info requested above, and I’m sure we can help you further. Sprinks "Andy B" wrote: Hi I've set up a database with sales figures. I have a table (called Data) which has Date, Group, Sales, Budget, YTD and this holds the raw data which I need to display. I have created three queries using this info which is based on the date entered in a form. One query has 12 records (one for each Group) and each record has Sales, GP and Budget figures. The second query has another 12 records, the same as the first query, but for the same month last year. The third query has up to 144 records (depending on the month) as this selects the YTD figures. I now need to display this information. I thought about using a form - but I don't know how to draw the information from three different queries. I then set up a table, so I could push the info into that - with a single append query and a load of update queries, but I don't know how to set up the update queries to use the other three queries I've already written. I'm sure there must be an easy/easier way to do this. Am I coming at it from the wrong direction? Any help/advice/experience would be very much appreciated. Cheers. -- Andy. |
#3
|
|||
|
|||
Andy B wrote:
Hi I've set up a database with sales figures. I have a table (called Data) which has Date, Group, Sales, Budget, YTD and this holds the raw data which I need to display. I have created three queries using this info which is based on the date entered in a form. One query has 12 records (one for each Group) and each record has Sales, GP and Budget figures. The second query has another 12 records, the same as the first query, but for the same month last year. The third query has up to 144 records (depending on the month) as this selects the YTD figures. I now need to display this information. I thought about using a form - but I don't know how to draw the information from three different queries. I then set up a table, so I could push the info into that - with a single append query and a load of update queries, but I don't know how to set up the update queries to use the other three queries I've already written. I'm sure there must be an easy/easier way to do this. Am I coming at it from the wrong direction? Any help/advice/experience would be very much appreciated. Cheers. Build a new query using the 3 queries you have now. You can then use the new query to build your form. Not sure if this is the best way or not but it should work. gls858 |
#4
|
|||
|
|||
Hi
Thanks to both of you for your replies! What I would like the user to see is a table showing Groups down the left and headings (Sales, GP, YTD) across the top. The user selects a month from a drop-down box, and a shop from another (already done). As a result of this, I've written three queries, all based on the same Data table. This table has: Month (eg 01/03/05), Shop (eg CH), Dept (eg AP), Sales (just for that month) and GP (just for that month). It then shows Sales and GP for the same month last year and YTD figures. 01/03/05 CH AP 5000 2500 01/03/05 CH CK 2000 1000 etc. There are 5184 records (48 Months, 9 Shops, 12 Depts). The first query selects the 14 records that are for the Month, for the Shop (12 records, one for each Dept). The second query takes a year off the Month and selects 12 records that are for the same month and the same shop last year. The third query selects all of the records since the start of the year (of the month) for the same shop. I now have 3 queries that I want to bring together to display the form. The problem is that I can't see how! I suppose one option might be to create 3 subforms (one for each query) and bring those together on a form? I can't think of a way to join these 3 queries, though. Thanks again! -- Andy. "Sprinks" wrote in message ... Hi, Andy. Before I can advise you, please describe, or better still, diagram, what you'd like the user to see, and explain the generic purpose of your application in plain language. I suspect you're table is not normalized, which would making life more complicated, but I'll reserve judgment until I understand what you're trying to do. I can say the following, however: . If you're finding you need to run a number of insert queries just in order to display the data you're after, there's likely a much simpler approach. . Normally, YTD numbers would never be stored in a table, they would be calculated on-the-fly whenever they're needed in a calculated field in a query. . 'Date' is an Access-reserved word. Naming fields with reserved words can cause unpredictable behavior. Use something like 'SalesDate' instead. A Google search can give you a complete list of Access reserved words. . You don't mention a primary key. Virtually all Access tables should have a primary key as a unique record identifier. This can be a single field or multiple ones, although most developers I know use a simple AutoNumber key. If they want to prevent duplicate records of say, a combination of Group and Month, they add a No Duplicates index in table design view. . Although I suspect your solution will be simpler than you've described, one displays information from multiple tables or queries by joining them in a query by their common field, selecting the fields of interest, and basing the form on the query. For example, to display the customer name & address info plus the information about one of their orders on a form, you need information from the Customers and Orders tables. The query would join the two tables by the common field CustomerID, which is the primary key of Customers and the foreign key in Orders. You can do the same with queries. Post the info requested above, and I'm sure we can help you further. Sprinks "Andy B" wrote: Hi I've set up a database with sales figures. I have a table (called Data) which has Date, Group, Sales, Budget, YTD and this holds the raw data which I need to display. I have created three queries using this info which is based on the date entered in a form. One query has 12 records (one for each Group) and each record has Sales, GP and Budget figures. The second query has another 12 records, the same as the first query, but for the same month last year. The third query has up to 144 records (depending on the month) as this selects the YTD figures. I now need to display this information. I thought about using a form - but I don't know how to draw the information from three different queries. I then set up a table, so I could push the info into that - with a single append query and a load of update queries, but I don't know how to set up the update queries to use the other three queries I've already written. I'm sure there must be an easy/easier way to do this. Am I coming at it from the wrong direction? Any help/advice/experience would be very much appreciated. Cheers. -- Andy. |
#5
|
|||
|
|||
Sprinks wrote: Virtually all Access tables should have a primary key as a unique record identifier. This can be a single field or multiple ones, although most developers I know use a simple AutoNumber key. Interesting phrasing. I think you are placing the wrong significance on the term 'primary key'. In relational theory, primary key has no special meaning: a unique identifier is a unique identifier. For MS Access/Jet, 'primary key' has a special meaning i.e. it determines the physical order on disk. For other products, including SQL Server, physical ordering may be explicitly specified independent of the primary key using a clustered index. For MS Access/Jet, we have no choice: the primary key is the only way of specifying the physical order. So given that: 1) an autonumber is a random or monotonic numeric with the sole purpose of guaranteeing uniqueness; 2) in terms of keys, PRIMARY KEY is the equivalent of a UNIQUE constraint/index; 3) in terms of physical ordering, PRIMARY KEY has special meaning over a UNIQUE constraint/index then the choice of an autonumber as primary key is usually a poor one. Jamie. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Display "No Information" in calendar | cornetthd@(ZZZZZZ)yahoo.com | Calendar | 0 | February 25th, 2005 09:16 PM |
Create List of dates and information | James Stephens | Running & Setting Up Queries | 2 | November 11th, 2004 01:19 PM |
Display email address instead in Contact | Landshark | General Discussion | 5 | July 11th, 2004 10:25 PM |
Publisher 2003 hotfix package Update (KB 838901) | Brian Kvalheim - [MSFT MVP] | Publisher | 0 | May 7th, 2004 08:38 PM |