setting criteria to select information from records 1

[atray04]

I am having a problem with my current query. I have a query that looks at all of my records and if a value in a record is less than 3 show the whole record and generates them in a report.

My problem is that I would also like to have a report with only the values less than 3 within that report. I can not seem to selectively take out the values larger than 2 in each of my records. Does anyone else know how this might me done?

-Atray

 

[hymn]

> = Greater than
< = Less than

Hope this helps
Hymn

 

[atray04]

that will show me whole records, which I already have that query. I want it to select from my values in the records for example:
lets say I have records that contain these items item1, item2, item3, item4. Record1 contains item1 = 1, item2 = 4, item3 = 1, item4 = 1. Record2 contains item1 = 4, item2 = 4, item3 = 4, item4 = 4. With my current query , that uses criteria: < 3 , it will show all items in Record1 but not Record2. What I would like to happen is to Show Record1 but only show items < 3 which would be item1, item3, and item4 for this example. I hope this clears some things up.

-atray

 

[lespaul]

The reason you are having problems with this query is because your table is not normalized. Anytime you have a table with

Something1
Something2
Something3
Something4

then you have a one-to-many relationship and should have another table.

So, instead of

tblOrders
CustomerID
Order1
Order2
Order3
Order4

you should have:

tblCustomerOrders
OrderID
CustomerID

that way each customer can have many orders.

if your table was structured like this you would be able to do what you want. With the structure you have the output you want (showing just item1, item3 and item4) is not possible just using SQL.

Check out 'The Fundamentals of Relational Database Design' for more information on normalizing your database.

If you are unable to change the table structure, you are going to need to write a saved query that normalizes your data and use that as the source:

SELECT PKField, "Item1" As SourceField, Item1 as ItemValue FROM TableName
UNION
SELECT PKField, "Item2", Item2 FROM TableName
UNION
SELECT PKField, "Item3", Item3 FROM TableName
UNION
SELECT PKField, "Item4", Item4 FROM TableName

this will give you:

PKField SourceField ItemValue
PKRecord1 Item1 5
PKRecord2 Item1 2
PKRecord3 Item1 5
PKRecord1 Item2 2
PKRecord2 Item2 5
PKRecord3 Item2 1
PKRecord1 Item3 5
PKRecord2 Item3 3
PKRecord3 Item3 1
PKRecord1 Item4 4
PKRecord2 Item4 2
PKRecord3 Item4 3

Now that the data is normalized you can extract the information you need.

HTH

Leslie

 

[hymn]

How many item fields have you
if you put > or < in the critera for item field it should produce what you want

Hope this helps
Hymn

 

[lespaul]

Hymn,

Record1 contains item1 = 1, item2 = 4, item3 = 1, item4 = 1. Record2 contains item1 = 4, item2 = 4, item3 = 4, item4 = 4.

so now we have:

PKField item1 item2 item3 item4
PK1 1 4 1 1
PK2 4 4 4 4

atray wants to only return

PK1 item1 item3 item4

How are you going to write the select statement so it only selects fields item1, item3 and item4 from record #1? what if record#3 is:

PKField item1 item2 item3 item4
PK3 4 1 3 2

so now we want three fields from record #1 and two fields from record#3.

So now the results should look like:

PK item1 item3 item4
1 1 1 1
item2
3 1 2

Leslie

 

[atray04]

It would probably be best if I wrote it in sql. Since my tables are already populated. I am not very familiar with sql though. Here is the code that I got from what you wrote.

SELECT rptHousekeepingBelowAverageADAM, "Walkways" As SourceField, Walkways as ItemValue FROM tblHousekeeping
UNION
SELECT rptHousekeepingBelowAverageADAM, "Platforms", Platforms FROM tblHousekeeping
UNION
SELECT rptHousekeepingBelowAverageADAM, "Cabinet Tops", Cabinet Tops FROM tblHousekeeping
UNION SELECT rptHousekeepingBelowAverageADAM, "Inside Cabinets", Inside Cabinets FROM tblHousekeeping;

First off I am receiving a syntax error because of inside cabinets since there is a space I do not know the correct syntax for that.

So once this code has been implemented for all of my items they should be normalized. Since the query is in sql now, how do I input the criteria?

 

[atray04]

Oh and thank you for that answer. Im one step farther.

 

[lespaul]

since there is a space you need to surround it in brackets:

SELECT rptHousekeepingBelowAverageADAM, "Walkways" As SourceField, Walkways as ItemValue FROM tblHousekeeping
UNION
SELECT rptHousekeepingBelowAverageADAM, "Platforms", Platforms FROM tblHousekeeping
UNION
SELECT rptHousekeepingBelowAverageADAM, "Cabinet Tops", [Cabinet Tops] FROM tblHousekeeping
UNION SELECT rptHousekeepingBelowAverageADAM, "Inside Cabinets", [Inside Cabinets] FROM tblHousekeeping;

Leslie

 

[atray04]

ok, thank you that did it. I am having a few other problems though. I am recieving a input parameter from rptHousekeepingBelowAverageADAM. Also it seems that by normalizing it if a item number had the same value twice it would only show one of the values. For example, walkways is in 19 different records, but some of walkways values are the same. Maybe im wrong, maybe there is another problem. If you could shed some light on these problems that would be awesome. Thank you for your help.

-ATray

 

[lespaul]

when you get the parameter entry box, what parameter is it asking for? Where is the item number in your query? Is rptHouseKeepingBelowAverageADAM a field in the tblHousekeeping?

Leslie

 

[atray04]

it is asking for rptHouseKeepingBelowAverageADAM a field in the tblHousekeeping which is the name of the report I want to generate. I am not sure about the item number though.

 

[lespaul]

what are all the fields in tblHouseKeeping that you need to have in the report?

 

[atray04]

my items are:
ShiftID, InspectorID, AreaID, DepartmentID, Date, Score, Walkways, Platforms, Cabinet Tops, Inside Cabinets, Electrical, Panels, Pallets, Ladders, Shop Floors, Workbenches, Maintenance Cabinets, Equipment, Electrical Boxes, Conduit, Wires, Damaged Boxes, Broken, Frayed, Pressure, Grinders, Safety Latches, Duct Tape, Paint, Masonry, Seals, Caulking, Lights, Insulation, Doors, Leaks

these are all of my items, the first four refer to other tables.

 

[lespaul]

So if those are you fields why are you selecting field rptHousekeepingBelowAverageADAM?

and I thought you said it was all in one table? what are the other tables?

why don't you start over. Tell me:

the names of all the tables you need information from
the names of all the fields in each table
the primary key of each table and any foriegn keys
some sample data and what you want your results to look like (what you need to report).



Leslie

 

[atray04]

I would like to create a report called:
rptHousekeepingBelowAverageADAM
I am mainly using tblHousekeeping, but also, tblHousekeepingDepartment, tblHousekeepingInspector, tblHousekeepingArea, tblShifts

tblHousekeepingDepartment has 2 fields DepartmentID and Department

tblHousekeepingInspector has 2 fields InspectorID and Inspector

tblHousekeepingArea has 3 fields AreaID, DepartmentID, and Area

tblShifts has 2 fields Shift, and ShiftID

tblHousekeeping has ShiftID, DepartmentID, AreaID, InspectorID, Date, Score, Walkways, Platforms, Cabinet Tops, Inside Cabinets, Electrical, Panels, Pallets, Ladders, Shop Floors, Workbenches, Maintenance Cabinets, Equipment, Electrical Boxes, Conduit, Wires, Damaged Boxes, Broken, Frayed, Pressure, Grinders, Safety Latches, Duct Tape, Paint, Masonry, Seals, Caulking, Lights, Insulation, Doors, Leaks

ShiftID, DepartmentID, AreaID, and InspectorID are keys and have relationships.

These items: Walkways, Platforms, Cabinet Tops, Inside Cabinets, Electrical, Panels, Pallets, Ladders, Shop Floors, Workbenches, Maintenance Cabinets, Equipment, Electrical Boxes, Conduit, Wires, Damaged Boxes, Broken, Frayed, Pressure, Grinders, Safety Latches, Duct Tape, Paint, Masonry, Seals, Caulking, Lights, Insulation, Doors, Leaks

are what I want my query to be based on and each item has either a value of 1, 2, 3, 4, or 5. And I only want items with a value of either a 1 or a 2 to be shown on my report.

Sample data in old format:

Individual Scores:
Walkways 3 Maintenance Cabinets 1
Platforms 2 Equipment 2
Cabinet Tops 1 Electrical Boxes 3

Sameple data in new format:

Individual Scores:
Platforms 2 Equipment 2
Cabinet Tops 1
Maintenance Cabinets 1

I hope this helps and thank you again for taking the time and effort to help me.

-ATray

I guess I should point out that the values of Area, Department, Inspector, Shift, Score, and Date just go at the top of the report.

 

[lespaul]

Ok, you need to create a query:

SELECT ShiftID, DepartmentID, AreaID, InspectorID, [Date], Score, "Walkways" As Item, Walkways As ItemValue FROM tblHousekeeping
UNION
SELECT ShiftID, DepartmentID, AreaID, InspectorID, [Date], Score, "Platforms", Platforms FROM tblHousekeeping
UNION
SELECT ShiftID, DepartmentID, AreaID, InspectorID, [Date], Score, "Cabinet Tops", [Cabinet Tops] FROM tblHousekeeping


for all the fields in your big table. Save this query as:

qryNormalHouseKping

now, create another query. When you open the new query, Access prompts you for the tables to add to the query. Add your lookup tables (Department, shift, area and inspector) now add the QUERY qryNormalHouseKping. Add the relationships between your lookup tables and the QUERY qryNormalHouseKping. Now select all the fields you need for your report and you should be good to go!






Leslie

 

[atray04]

Thank you that is what I was looking for. Is there any way of having each record under inspector with that inspectors items instead of 1 item in each record? Right now my report shows all of my record, but I would like each record to have all of the items from that inspector on that date. Is this just a simple change? Besides that everything is working.

 

[atray04]

I think I know what to do. Would I have to get rid of Item and ItemValue and change it back to the item names as the fields with there values and normalize it that way?

 

[lespaul]

when you created the report you had the opportunity to set the report grouping. If you add the inspector to the report grouping, it should do what you want, but I don't do Access Reports so I don't have a CLUE where you would go to change the report grouping!

Thanks for the star! Sorry I couldn't help with this issue!

leslie