Display of information

[Guest]

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.

 

[Guest]

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

 

[gls858]

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

 

[Guest]

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!

 

[onedaywhen]

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.

--