Type

  • Thread starter Thread starter NickH
  • Start date Start date
N

NickH

Hi

I have a table which I need to make a query on the type.i.e

Table Header
Session ID Type Status Freq Scale
1 0 0 0
2 1 0 0
3 2 0 0
4 3 0 0
5 1 0 0

In the "Type" column the numbers represent descriptions

0 = ANT
1 = Cable
2 = Other
3 = AmpCor

So far I have built a Query which will recognise one state
ie. Condition:IIf([Type]=0,"ANT").
How do I complete the the query for the other three, as
you can see I am a novice at this.


Thanks

Nick
 
Create a table with fields:
PartTypeID Number
PartType Text

You can then include this table in your query, joined by PartTypeID, and get
the PartType easily.

(Note: Type and TypeName are special names in Access, hence the suggestion
to use PartType).
 
Hi Allen

I have tried implementing what you have suggested and it
works fine.
I do have another question which has just come up, in the
field "Status" the data displayed is either 0 or 1.
0 = Not displayed
1 = Displayed


Select*
From AmpCorDescriptionQuery
WHERE [Status]=1;

Is it possible to include the above SQL statement, along
side or within the SQL in query statement below.


SELECT tblpartType.PartTypeID, tblpartType.PartType,
[Ampcor Point].Type, [Ampcor Point].Point, [Ampcor
Point].Freq, [Ampcor Point].Amp
FROM tblpartType INNER JOIN [Ampcor Point] ON
tblpartType.PartTypeID = [Ampcor Point].Type;

Please accept I am a Novice

This would be very useful and helpful.

I hope I haven't confused.


Thanks for your help


Nick

-----Original Message-----
Create a table with fields:
PartTypeID Number
PartType Text

You can then include this table in your query, joined by PartTypeID, and get
the PartType easily.

(Note: Type and TypeName are special names in Access, hence the suggestion
to use PartType).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which I need to make a query on the type.i.e

Table Header
Session ID Type Status Freq Scale
1 0 0 0
2 1 0 0
3 2 0 0
4 3 0 0
5 1 0 0

In the "Type" column the numbers represent descriptions

0 = ANT
1 = Cable
2 = Other
3 = AmpCor

So far I have built a Query which will recognise one state
ie. Condition:IIf([Type]=0,"ANT").
How do I complete the the query for the other three, as
you can see I am a novice at this.
Click to expand...


.
Click to expand...
 
That should not be a problem.

1. Open your main query in design view.

2. Drag the table that contains the Status field into the query.
Make sure you get the desired joins between your queries.

3. Drag the Status field into the query grid.
In the Criteria row under this field, enter:
1

If necessary, you can use AmpCorDescriptionQuery at step 2 instead of the
table.
 
Hi Allen

Yes it works in principle, and selects the Blocks of
entries, as I will show in an example below, it is leaving
in the a complete block of data even where the status
is "0".
In my main query

Three tables now exist, original it works fine with two
table as from your advise.

Therefore I have at present.

TABLE:tblpartType (partTypeID) joined to (Type) of
TABLE:AmpCor and from TABLE:AmpCor (Session ID) joined to
(Session ID) of TABLE:Ampcor Description.

The Status field in the main query contains all "1",
therefore the two examples below will show the result

New Query:
PartTypeID PartType Point Freq Amp Status
0 ANT 1 1 2 1
0 ANT 2 2 3 1
1 Cable 1 1 2 1

Old Query
PartTypeID PartType Point Freq Amp Status
0 ANT 1 1 2 1
0 ANT 2 2 3 1
1 Cable 1 1 2 0

The latter is correct, as from the original tables the
third record the status is "0", the former though is
incorrect, so for each Session Block which contains a
Status "1" this will print out all records for the four
types although some records original had the status "0".
The types of join are the same were the fields selected
are both equal 1:1.

I have tried other Join settings but no joy.


It must be something I am doing wrong

but again any help

would be much appreciated.

Thanks

Nick
-----Original Message-----
That should not be a problem.

1. Open your main query in design view.

2. Drag the table that contains the Status field into the query.
Make sure you get the desired joins between your queries.

3. Drag the Status field into the query grid.
In the Criteria row under this field, enter:
1

If necessary, you can use AmpCorDescriptionQuery at step 2 instead of the
table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I do have another question which has just come up, in the
field "Status" the data displayed is either 0 or 1.
0 = Not displayed
1 = Displayed


Select*
From AmpCorDescriptionQuery
WHERE [Status]=1;

Is it possible to include the above SQL statement, along
side or within the SQL in query statement below.


SELECT tblpartType.PartTypeID, tblpartType.PartType,
[Ampcor Point].Type, [Ampcor Point].Point, [Ampcor
Point].Freq, [Ampcor Point].Amp
FROM tblpartType INNER JOIN [Ampcor Point] ON
tblpartType.PartTypeID = [Ampcor Point].Type;

Please accept I am a Novice
Click to expand...


.
Click to expand...
 
Hi Allen

It seems to have resolved the problem.

So Thank you for the help.


Nick

-----Original Message-----
That should not be a problem.

1. Open your main query in design view.

2. Drag the table that contains the Status field into the query.
Make sure you get the desired joins between your queries.

3. Drag the Status field into the query grid.
In the Criteria row under this field, enter:
1

If necessary, you can use AmpCorDescriptionQuery at step 2 instead of the
table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I do have another question which has just come up, in the
field "Status" the data displayed is either 0 or 1.
0 = Not displayed
1 = Displayed


Select*
From AmpCorDescriptionQuery
WHERE [Status]=1;

Is it possible to include the above SQL statement, along
side or within the SQL in query statement below.


SELECT tblpartType.PartTypeID, tblpartType.PartType,
[Ampcor Point].Type, [Ampcor Point].Point, [Ampcor
Point].Freq, [Ampcor Point].Amp
FROM tblpartType INNER JOIN [Ampcor Point] ON
tblpartType.PartTypeID = [Ampcor Point].Type;

Please accept I am a Novice
Click to expand...


.
Click to expand...
 
Back
Top