Help on access 2003 query

I have this table:

ITEMS
ITEM ASSY PAINT SHIP
Chair A001 P001 S001
Table A002 P002 S002
Bed A003 P003 S003


Then I have a form and I capture the fallowing data

DEPARTMENT
DATE
ITEM
UNITS


I need one query that give me this data.
QUERY 1
ITEM UNITS DEPT OPER
Chair 2 ASSY A001
Chair 3 PAINT P001
Bed 4 ASSY A003
Bed 3 PAINT P003
Table 2 SHIP S002
Table 1 ASSY A002

So this means that each item may have a different
operation base on the department.

and there's where I need help.

I will preciated any help the more detail the better since I'm just start
using access.

Thanks.

Hi -

I think your ITEMS table needs to be restructured. Assuming that Assy, Paint
and Ship are departments, and A001, P001 etc are OPER's, then it should be
something like this:

ITEMS
ITEM DEPT OPER
Chair ASSY A001
Chair PAINT P001
Chair SHIP S001
Table ASSY A002
...

And that is not really an items table, either, because it has a compound
Primary Key ( ITEMS+DEPT)

From what you have shown, you need at least four tables:

ITEMS
DEPT
ITEM_DEPT (That's the one above)
ITEMS_RECEIVED (or whatever it is you form is getting)

What table does the form have as its recordsource? Without another table, you
cannot have a query that will do what you need.

John



sammy wrote:
I have this table:

ITEMS
ITEM ASSY PAINT SHIP
Chair A001 P001 S001
Table A002 P002 S002
Bed A003 P003 S003

Then I have a form and I capture the fallowing data

DEPARTMENT
DATE
ITEM
UNITS

I need one query that give me this data.
QUERY 1
ITEM UNITS DEPT OPER
Chair 2 ASSY A001
Chair 3 PAINT P001
Bed 4 ASSY A003
Bed 3 PAINT P003
Table 2 SHIP S002
Table 1 ASSY A002

So this means that each item may have a different
operation base on the department.

and there's where I need help.

I will preciated any help the more detail the better since I'm just start
using access.

Thanks.

--
Message posted via http://www.accessmonster.com

Hi

I need to use only one item number and this item number will have a
different operation numbers because this same item number will be used by
diferent departments.

I do have another table in this table I have the fallowing fields

DEPT
DATE
OPER
UNIT

This table will be populated from the form and when he enters the department
and item number the program assigs the correct operation.

I hope I did't confuse you more, my english it's not that good but I do have
a better spanish

Thanks for all your help.





"J_Goddard via AccessMonster.com" wrote:

Hi -

I think your ITEMS table needs to be restructured. Assuming that Assy, Paint
and Ship are departments, and A001, P001 etc are OPER's, then it should be
something like this:

ITEMS
ITEM DEPT OPER
Chair ASSY A001
Chair PAINT P001
Chair SHIP S001
Table ASSY A002
...

And that is not really an items table, either, because it has a compound
Primary Key ( ITEMS+DEPT)

From what you have shown, you need at least four tables:

ITEMS
DEPT
ITEM_DEPT (That's the one above)
ITEMS_RECEIVED (or whatever it is you form is getting)

What table does the form have as its recordsource? Without another table, you
cannot have a query that will do what you need.

John



sammy wrote:
I have this table:

ITEMS
ITEM ASSY PAINT SHIP
Chair A001 P001 S001
Table A002 P002 S002
Bed A003 P003 S003

Then I have a form and I capture the fallowing data

DEPARTMENT
DATE
ITEM
UNITS

I need one query that give me this data.
QUERY 1
ITEM UNITS DEPT OPER
Chair 2 ASSY A001
Chair 3 PAINT P001
Bed 4 ASSY A003
Bed 3 PAINT P003
Table 2 SHIP S002
Table 1 ASSY A002

So this means that each item may have a different
operation base on the department.

and there's where I need help.

I will preciated any help the more detail the better since I'm just start
using access.

Thanks.

--
Message posted via http://www.accessmonster.com