Tough Group Query

[dsrahul]

Dear Friends,

There are 8 tables named Group1 to Group 8
Each one has Same Fields
Ex:
ItemNo,Description,Required

Each Group may not have same number of records.
Some has 5 Item some 50 or more.

Another Table is there called "Main"
This will accept Duplicate Itemno.
Which is having Itemno,Description quantity,Price.
&
The last table is Called "Final",where Itemno is Indexed,
Which has,ItemNo,Description,Instock,Outfor Fields

Where ItemnoIndex mask is like "Yes,No Duplicates" under Itemno field.
That means this "Final" Table will not accept Duplicate Itemno.

Problem is:
How do I get to read records Groupwise? in one button Click.
& display them in MsFlexigrid or in a Combobox?

If this is possible then show the total count of the items in order.
EX:
Item1=5
Item2=3
etc.
Apart from this :
It is needed to go to each Group & see how many items are there & look at the "Required" Field , then Go to the "Final" table match the item ,.deduct the Instock as per Required Field thus finishing one by one all the Groups.
Then adding For107 to the Outfor Field.

Remember all the 8 Tables will be checked for Itemno,& Required.

How this is done?

[jp140768]

Hi,

This is more of a database question than a VB one isn't it? What databse are you using?

If I was doing it, I think I'd create a temporary table which contains records from the 8 group tables where the item no matched your parameter, and then join this to the Final table, and use this as the recordset for the grid.

What database are you using? Do you want the grid to be bound or unbound?

JP

[dsrahul]

Dear JP,
I am Using MsAccess as Database.
there is a small glitch to create a temp table
coz some items are common.
As you can see from the database.
no they are all unbound.
I think somewhere SQL Query is required.
But now idea how to implement it.
Do Reply
Rahul

[sotoasty]

Why not make a modification to the DB to include the Group Number. So instead of having 8 group tables you have 1 that is like the following.

ItemNo,Description,Required,GroupNumber

That makes it much easier for your select statements.

"SELECT * FROM Group WHERE GroupNumber=6;"

or

SELECT GroupNumber, COUNT(ItemNumber) AS ReqItems FROM Group WHERE Required='Y' GROUP BY GroupNumber;"

Which would return Something like...
1 4
2 6
3 30
4 10
5 3
6 15
7 109
8 0

[dsrahul]

Sotoasty,

Thanks for the reply, But I feel that you Overlooked the Common Field.
This common Field is logical(Yes/No)
There are some components which are common
Coz virtually there are 16 Groups
Namely:
107Group1 to 107Group8
&
82Group1 to 82Group8

See the DB you'll understand.

Okay let me give you the entire project.
See & modify please.