Urgent

  • Thread starter Thread starter Niklas Östrergren
  • Start date Start date
N

Niklas Östrergren

Does your tables have any unique field that linkt hte records to each other?
If so that would be a little bit easyer to group the data the way you like.

What I can see from your msg is that both tables are ascended sorted so you
could first sort the tables the way you want and then add a new field
(AutoNumber, lets call it [ExpenseID] and [SaleID]) to each table. This will
give the first record in each table ID no 1, next record 2 and so on.

Whist this in mind you create a relationship between the tables in the query
selecting all records from the tables with most records in. And finaly
creating a query like this:

SELECT tblExpense.ExpenseAmount, tblSale.SaleAmount
FROM tblExpense LEFT JOIN tblSale ON tblExpense.ExpenseID = tblSale.SaleID;

In this example I have named the tables tblExpense and tblSale and I also
have selected all records from tbalExpense since that´s the table (in my
example) that have most records.

What I´m thinking of is if you realy want to present the result like this?
Don´t you need to group it by date or something else like employee or what
ever? I don´t know your aplication so I´m a little out in the dark here but
just selecting amouts sorted byt the way they where entered in the tables
are rare and, if you ask me, not very useful. Just a thought!

// Niklas
 
There are two tables

Expense Table
Amount date
5000 01/01/2004
5000 01/01/2004
100 01/01/2004

Sal Table
Amount date
10000 01/01/2004
400 01/01/2004
500 01/01/2004
100 01/01/2004



expense amount sale amount
5000 10000
5000 400
100 500
100

I want the data like this against 01/01/2004 date, can any body give me
query that how I fulfill that one.

Thanks
NOOR
 
Thanks for your prompt reply but this is just to inform you that there is no
relationship b/w these two tables. The data is as follows , please tell me
any solution, what I have to do for this. Against date I want the result
Expense Table
Amount date
5000 01/01/2004
5000 01/01/2004
100 01/01/2004

Sal Table
Amount date
10000 01/01/2004
400 01/01/2004
500 01/01/2004
100 01/01/2004



expense amount sale amount
5000 10000
5000 400
100 500
100
Click to expand...

Niklas Östrergren said:
Does your tables have any unique field that linkt hte records to each other?
If so that would be a little bit easyer to group the data the way you like.

What I can see from your msg is that both tables are ascended sorted so you
could first sort the tables the way you want and then add a new field
(AutoNumber, lets call it [ExpenseID] and [SaleID]) to each table. This will
give the first record in each table ID no 1, next record 2 and so on.

Whist this in mind you create a relationship between the tables in the query
selecting all records from the tables with most records in. And finaly
creating a query like this:

SELECT tblExpense.ExpenseAmount, tblSale.SaleAmount
FROM tblExpense LEFT JOIN tblSale ON tblExpense.ExpenseID = tblSale.SaleID;

In this example I have named the tables tblExpense and tblSale and I also
have selected all records from tbalExpense since that´s the table (in my
example) that have most records.

What I´m thinking of is if you realy want to present the result like this?
Don´t you need to group it by date or something else like employee or what
ever? I don´t know your aplication so I´m a little out in the dark here but
just selecting amouts sorted byt the way they where entered in the tables
are rare and, if you ask me, not very useful. Just a thought!

// Niklas



Noorali Issani said:
There are two tables

Expense Table
Amount date
5000 01/01/2004
5000 01/01/2004
100 01/01/2004

Sal Table
Amount date
10000 01/01/2004
400 01/01/2004
500 01/01/2004
100 01/01/2004



expense amount sale amount
5000 10000
5000 400
100 500
100

I want the data like this against 01/01/2004 date, can any body give me
query that how I fulfill that one.

Thanks
NOOR
Click to expand...
Click to expand...
 
Database is Access


Noorali Issani said:
Thanks for your prompt reply but this is just to inform you that there is no
relationship b/w these two tables. The data is as follows , please tell me
any solution, what I have to do for this. Against date I want the result
Expense Table
Amount date
5000 01/01/2004
5000 01/01/2004
100 01/01/2004

Sal Table
Amount date
10000 01/01/2004
400 01/01/2004
500 01/01/2004
100 01/01/2004



expense amount sale amount
5000 10000
5000 400
100 500
100
Click to expand...

Niklas Östrergren said:
Does your tables have any unique field that linkt hte records to each other?
If so that would be a little bit easyer to group the data the way you like.

What I can see from your msg is that both tables are ascended sorted so you
could first sort the tables the way you want and then add a new field
(AutoNumber, lets call it [ExpenseID] and [SaleID]) to each table. This will
give the first record in each table ID no 1, next record 2 and so on.

Whist this in mind you create a relationship between the tables in the query
selecting all records from the tables with most records in. And finaly
creating a query like this:

SELECT tblExpense.ExpenseAmount, tblSale.SaleAmount
FROM tblExpense LEFT JOIN tblSale ON tblExpense.ExpenseID = tblSale.SaleID;

In this example I have named the tables tblExpense and tblSale and I als o
have selected all records from tbalExpense since that´s the table (in my
example) that have most records.

What I´m thinking of is if you realy want to present the result like this?
Don´t you need to group it by date or something else like employee or what
ever? I don´t know your aplication so I´m a little out in the dark here but
just selecting amouts sorted byt the way they where entered in the tables
are rare and, if you ask me, not very useful. Just a thought!

// Niklas



Noorali Issani said:
There are two tables

Expense Table
Amount date
5000 01/01/2004
5000 01/01/2004
100 01/01/2004

Sal Table
Amount date
10000 01/01/2004
400 01/01/2004
500 01/01/2004
100 01/01/2004



expense amount sale amount
5000 10000
5000 400
100 500
100

I want the data like this against 01/01/2004 date, can any body give me
query that how I fulfill that one.

Thanks
NOOR
Click to expand...
Click to expand...
Click to expand...
 
Back
Top