Query Criteria Help Please

[james_halliwell]

Hi Everyone

I'm new to the site so here goes, I am trying to make a query that runs off two table I will try to explain what I am trying to do

Table 1 (Target Turn Rate)
This table has a record of kits that we have and how well they should be doing i.e.

AcName........... Kit......... TargetRate.......... LowProforming
Leeds ...............as400 ..........20 .......................10
Bradford............ sigma.......... 21........................ 8
Dewsbury.......... bread.......... 23........................ 16

Table 2 (KitUsage)
This table is a download off a programme off Cognos it tells us how many time kits are turning i.e.

AcName........... Kit.............. TurnRate
Leeds.............. as400 ..............18
Bradford ...........sigma ..............23
Dewsbury.......... bread.............. 21

the query that I’m try to get is that it only shows kits that are turning less than the target rate please could anyone help, If this is really simple I am very sorry but I have just been on a two day access course (basics)
and my boss now thinks I can do it all (Don't All Bosses)

Any Help would be kindly appreciated

Thanks
James

 

[MSAccessRookie]

Hi Everyone

I'm new to the site so here goes, I am trying to make a query that runs off two table I will try to explain what I am trying to do

Table 1 (Target Turn Rate)
This table has a record of kits that we have and how well they should be doing i.e.

AcName........... Kit......... TargetRate.......... LowProforming
Leeds ...............as400 ..........20 .......................10
Bradford............ sigma.......... 21........................ 8
Dewsbury.......... bread.......... 23........................ 16

Table 2 (KitUsage)
This table is a download off a programme off Cognos it tells us how many time kits are turning i.e.

AcName........... Kit.............. TurnRate
Leeds.............. as400 ..............18
Bradford ...........sigma ..............23
Dewsbury.......... bread.............. 21

the query that I’m try to get is that it only shows kits that are turning less than the target rate please could anyone help, If this is really simple I am very sorry but I have just been on a two day access course (basics)
and my boss now thinks I can do it all (Don't All Bosses)

Any Help would be kindly appreciated

Thanks
James

The general form for a query of this type, is shown below.

[INDENT][B]Select[/B] { Whatever you want  }
[B]From[/B] Table1 Inner Join Tabel2 On { Whatever they Join On }
[B]Where[/B] { Any Condition to meet }
[B]Order By[/B] { Whatever you sort on }
[/INDENT]

This makes your query something like the following:

[INDENT][B]Select[/B] Table1.AcName, Table1.Kit, Table1.TargetRate, Table2.TurnRate 
[B]From[/B] Table1 Inner Join Tabel2 On Table1.Kit = Table2.Kit
[B]Where[/B] Table1.TargetRate < Table2.TurnRate
[B]Order By[/B] (Table1.TargetRate < Table2.TurnRate), Table1.Kit
[/INDENT]

This code should provide the list that you want, ordered first by the amount of the shortfall, then by the name of the Kit that is falling short (feel free to substitute as required).

Note 1: The Table/Column names are for descriptive purposes only. You will need to substitute your own.

Note 2: If there are any characters in the Table/Column names that are not either Numbers or letters, or if any of the names are the same as an Access reserved word (Date, Time, Number, etc.), you may want to consider renaming them.

 

[james_halliwell]

Really sorry but still really new to access excel im fine but this is quite different how do you create an inner join i have the relationship bit done but i think i might of switched off when leraning join types

sorry the newbie question

 

[MSAccessRookie]

Really sorry but still really new to access excel im fine but this is quite different how do you create an inner join i have the relationship bit done but i think i might of switched off when leraning join types

sorry the newbie question

If you are in SQL Mode:

• Type the SQL Statement EXACTLY as I did, substituting table and column names are required.

If you are in Design Mode:

• Select Both tables from the table list.
• Select the Join Column from one of them drag it over to the Join Column in the other table. An Inner Join is the Default result.
• Drag the appropriate column names down to the display area.
• Add conditions as required.

 

[james_halliwell]

Thanks for your help with this and the quick responce

Many Thanks

James

 

[james_halliwell]

Hi MS_Access_Rookie

I seem to be struggling with this as i said i am new to access hoping to do more courses but getting my head around issues is quite a task

I have attached the two tables i am trying to get the results from i was wondering if you could have a look and then i could see what you have done
its if the turn 12 months (Instrument Ut Table) is lower than the low proforming field (target Turn Rate Table)

i tryed putting it in the builder and still no joy any help would be appreciated

I should also mention im trying this access 2007

Thanks

 

[wdrspens]

James

The first problem that you must sort out is which fields in the two tables you want to compare. I think it is the field called "Low Proforming" in the table "05 Target Turn Rate" which is a text field although it only holds numbers, and, probably, the field "Turn 12 month" in the table "02 Instruments Utilisation" which is a Number field of double size.
If I am right, then you must set them both to the same Field Size in their respective tables. Set them both to Numbers - Double (although I would use integer, it is quicker unless you know you are going to use decimals)
Then you should create a relationship between the two Tables, I suggest "Kit Name" under table "05 Target Turn Rate" and "Instrument Kit" under table "02 Instruments Utilisation".
Finally a Select Query using those two tables and set the criteria under "Turn 12 month" to be < or > than [Target Rate].

However those two fields MUST be the same Field Size, either both text or both numbers, and if numbers, both integer or double or whatever, and if text both the same size.

I hope that helps

David

 

[wdrspens]

I have tried several times to upload the amended database, but even though it is within the size constraint, it will not upload.

If you care to let me have your email address I will email it to you.

David

 

[james_halliwell]

thanks to you both for your help and quick responce will be back soon to pick your knowledge no doubt

James