[bladecs2002]

Hi,
I have started may own bussines some time ago...
Since then I have been able to make a Access database in wich I introduce my custumers...
1st question:
My company sells energy so what I cant figure out how to link the custumers table to another table that stores the information about monthly energy consumption for each custumer.
Here are the column that I think I need...
CustumerID SerialNumber(for the energy counter) Energy ... and I realy dont know how should I cont each month energy consumpltion. I need to store each month because I need to make a "consumers profile" every year...

If you can help me, guys, I would apreciate very much an maybe I can contribute too.

P.S. I wrote SQL server because in the future I hope the database I make now will help me create one in SQL server with a front end and all...

[OBP]

bladecs, welcome to the Forum.
Can you post a Printscreen copy of your table Relationships please, or better still a zipped Copy of the database in Access 2000-2003 format. Just ensure that there is no Personal Data in the tables.
Perhaps you would like to read this article I wrote on Tables and Relationships and attached here
http://forums.techguy.org/tech-tips-...se-basics.html

[bladecs2002]

The database is not in english... and is not normalized...
I am working to normalize it. In the end it will have about 10 related tables.
The thing that is bugging me it that I have no idea how i should do the monthly energy consumption. That table is called Consum.

[OBP]

It is a simple enough error, you have used the Client_ID as the key field for the Consum Table, wheras it should have it's own Key field which I have called ConsumID.
I have set the Relationship as a One (Customer) to Many (Consum) so that you can have as many Consum readings as you like.
I think that you should also have a Reading Date Or Reading Month field in the Consum table.

[bladecs2002]

Thank you for your answer, OBP!
As I am normalizing the tables, i'll do as you say and make a column for the date of the reading.
I'll post the DB to show the result as I finish the tables.
I can't think of all the things I have to take into account at once but as i progress I'll probabaly have more questions.
BTW, thanks for the article, it was helpfull

[OBP]

Just let me know if you need any more help.

[bladecs2002]

Hello, again!
I'm back to work. I managed to create the tables, queries and forms to fill multiple tables.
What I need to do now is to group all the data (monthly energy readings) for each customer in columns, not rows... I really have no idea how should I start

This is how I get the query:

Custumer1 UniqueID Date CounterData
Custumer1 UniqueID Date CounterData
Custumer2 UniqueID Date CounterData
and so on, with multiple rows for each customer

This is how I need to get the info:

Customer1 UniqueID Date1 CounterData1 Date2 CounterData2

Later on i'll need to create equations to subtract from CounterData2 the CounterData1 for the last month's reading...
Thank you!

[OBP]

Can you post a copy of the database as it is now please.
I think you will need to use a Crosstab Query to do what you describe.

[bladecs2002]

I am already using crosstab queries and made forms that fill in the tables...
I think I need a query or a report... or something that collects the data and transposes it and also makes it easier to put int in a equation...
I have no idea... maybe I should make an excel file where I can show what I have to input and what should be autocalculated and how i would like to output the information...

[OBP]

The excel file would be a good idea.

[OBP]

Can you tell me the Tables and fields you want in the Query?

[bladecs2002]

Query: Istoric comsumuri has the columns I need except that for each Custumer(Beneficiar) i need the Reading Date (Data Citire) and Index each time they apear in the Consumuri table, on the same row.
Like that:
Beneficiar | Serie contor | Data citire | Index| Data citire | Index ... i think this is the solution that i'm after
P.S. Working on excel table

[OBP]

That would be better as a Crosstab with

-----------------------Data citire | Data citire | Data citire
Beneficiar| Serie contor |--Index---|--Index---|--Index

To do it the way that you want I think will require a Temporary Table to be built each time you want to run the report.

[bladecs2002]

The green cells are for data input wich will be done by forms to multiple tables, and the red ones are for output and will be mailmerged or printed from a report.
I hope I've been explicit enough

[OBP]

blade, how are you going to print 12 months worth of data on one sheet of paper in that format?