Please help with complex query/queries...

[petrosky]

Hi,

It has been a while since I got my hands "dirty" with access so I hope someone can shed some light for me.

I have a very large table containing transaction history. It dates back to 2005 and is approx 700K records.

My data looks like the following sample data.

[tt][b]
DateSold    Quan Product    Group  Sale   Cost Etc.[/b]
12/01/05    10   Widget1    100    $99    $79
13/01/05    5    Widget2    101    $299   $200
14/01/05    10   Widget3    102    $399   $300
15/01/05    10   Thingo1    200    $999   $800
15/01/05    10   Thingo2    201    $899   $700
15/01/05    10   OtherProd  300    $399   $300
15/01/05    10   OtherProd2 300    $999   $800
15/01/05    10   Thingo2    201    $899   $700
[/tt]

Basically although the widgets are from separate groups, I need them to be treated as once entity so I can provide monthly/yearly comparison on them. In this limited example there would be 25 Widgets sold for Jan 05 and I would then query the other groups of like products together.

I am thinking I will need a many to many joining table but can't see how I am going to combine the groups in this query.

I hope this post makes some sense and I hope someone can point me in the right direction.

Regards,

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[dhookom]

You could create a table of every unique Product value as well as a field that stores its Group. This could be a foreign key field to a table of Groups.

Duane
Hook'D on Access
MS Access MVP

 

[petrosky]

Hi Duane,

Thanks very much. Here is how I ended up sorting it out...

Products Table as above

[tt]

DateSold    Quan Product    Group  Sale   Cost Etc.
12/01/05    10   Widget1    100    $99    $79
13/01/05    5    Widget2    101    $299   $200
14/01/05    10   Widget3    102    $399   $300
15/01/05    10   Thingo1    200    $999   $800
15/01/05    10   Thingo2    201    $899   $700
15/01/05    10   OtherProd  300    $399   $300
15/01/05    10   OtherProd2 300    $999   $800
15/01/05    10   Thingo2    201    $899   $700
[/tt]

Groups Table

[tt]
Group Description
100   Small Widgets
101   Medium Widgets
102   Large Widgets
200   Small Thingo
201   Medium Thingo
[/tt]

JoinTable

[tt]

GroupID TranslogID Description
100     100        Widgets
101     101        Widgets
102     102        Widgets
200     200        Thingos
201     201        Thingos
[/tt]

Now the query can be grouped and summed correctly.

Regards,

Peter.

Remember- It's nice to be important,
but it's important to be nice

 

[fneily]

First your Transaction History table and your Products table look the same. I guess you mean them to be the Transaction table.

This transaction table is not normalized. The field "Group" shouldn't be there. Neither should Cost. Also, you have a description for Product. If your tables were constructed by the rules, you'd have a ProductID. (unless Widget1, widget2, etc are your primary key - can't tell)
So it seems the problem is in the main tables. Your Product table show look like:
tblProduct
ProductID
Description
Cost
Category

So you'd have records with descriptions Small Widget, Medium Widget, etc and all would have the same category - Widget. Now it's easy to group and sum.

All the fields in the transaction table should only pertain to the transaction. Group and cost don't have anything to do with a transaction.
You may want to see:
Fundamentals of Relational Database Design

http://r937.com/relational.html

 

[petrosky]

Hi fneily,

The data certainly isn't normalised. Employees, Groups, Products AND transactions should all be separate tables.

Unfortunately, this is how the data will be coming to me monthly to be appended to the transaction table.

The sole purpose of this database is to move away from reporting process that was taking place in Excel in monthly snapshots and to be able to give more information on trends for various groups.

I have more testing but I believe my work-around should suffice for what is an arbitrary grouping of groups.

FYI. We are actually a camera store. Real world example of these groups of groups would be eg. Compact Digital Cameras.
Group 101 - 1 Megapixel
Group 102 - 2 Megapixel
Group 103 - 3 Megapixel
etc.

Regards,

Peter.



Remember- It's nice to be important,
but it's important to be nice

 

[fneily]

but it's important to be nice". Hmmm. Then I'll nicely disagree with what you believe to be your intentions. You're not moving away from Excel into an Access database environment. You're bastardizing the way Access should be used. Since you mentioned trends, if your tables were set up correctly, you could even use Pivot Tables to show percentage of sales vs each group then draw business decisions. And then there's the basic graphs, seasonal trends, etc.
Of course, my viewpoint is moot.

 

[petrosky]

fneiy,

Fair enough. Although, I still think you are missing the original point that individual groups need to be combined in an arbritrary fashion to become separate groups of entitities...

The groups are not fixed in stone.
New products are added daily.
The original database is an antique PICK multivalue system.

Thanks for your suggestions though.

Regards,

Peter.


Remember- It's nice to be important,
but it's important to be nice

 

[lespaul]

So you are saying that you get all this information from some outside source and you've created this "transaction" table from that information? Or you get the "transaction" table from the outside source?

Either way it doesn't matter. If you have an Access database that is taking data from an outside source, you should set up YOUR database correctly and then transform the outside information into your correctly designed normalized tables and then you can do exactly what needs to be done.

Leslie

Have you met Hardy Heron?