Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: I am sorta getting the hang of MS ACCESS, just neede help with a query!

  1. #1
    FLAC
    Join Date
    Apr 2005
    Location
    Queens, New York
    Posts
    1,386

    I am sorta getting the hang of MS ACCESS, just neede help with a query!

    OK well i have been working on my little hw for a while now. I am trying to create a query the will look up in the DB, which products have fallen below the reorder level. There are 2 fields 1 is Quantity on Hand" and the other is Reorder Level. What i need the query to do is Look up which quantity on hand has fell below the reorder level. Can some1 show me how to do that?

    here is the document:

    http://wps.prenhall.com/wps/media/ob...estionfile.mdb
    2002 Mitsubishi Galant
    Progress: 90% [-▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓- ->

    Carputer Specs:
    Via M10K
    512mb Ram
    60GB HDD
    Souund Blaster Audigy2 NX
    OPUS ITX PC Case


  2. #2
    Variable Bitrate StrataG's Avatar
    Join Date
    Aug 2005
    Location
    New York
    Posts
    425
    SELECT * FROM table_name WHERE stock<=reorder_level

    Obviously replace the fields and table name appropriately. That will give a data set of all products that fit your requirements. If you only reorder when the stock has fallen below the reorder level, then replace <= with <

  3. #3
    FLAC
    Join Date
    Apr 2005
    Location
    Queens, New York
    Posts
    1,386
    Quote Originally Posted by StrataG
    SELECT * FROM table_name WHERE stock<=reorder_level

    Obviously replace the fields and table name appropriately. That will give a data set of all products that fit your requirements. If you only reorder when the stock has fallen below the reorder level, then replace <= with <

    I dont follow?
    2002 Mitsubishi Galant
    Progress: 90% [-▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓- ->

    Carputer Specs:
    Via M10K
    512mb Ram
    60GB HDD
    Souund Blaster Audigy2 NX
    OPUS ITX PC Case


  4. #4
    Variable Bitrate StrataG's Avatar
    Join Date
    Aug 2005
    Location
    New York
    Posts
    425
    That is an SQL query that will produce the data set you are looking for. Create a query and select SQL query rather than using their pick and choose list. Not exactly sure of the option as I haven't used Access directly in years. There was an option for it and I don't think they'd remove it.

  5. #5
    Variable Bitrate StrataG's Avatar
    Join Date
    Aug 2005
    Location
    New York
    Posts
    425
    SELECT * - selects all fields from the table
    FROM table_name - tells what table to use
    WHERE stock<=reorder_level - narrows the query to only those rows where the stock is less than or equal to the reorder level.

  6. #6
    FLAC
    Join Date
    Apr 2005
    Location
    Queens, New York
    Posts
    1,386
    this is the SQL I get....
    SELECT [Product Table Query].[Quantity on Hand], [Product Table Query].[Reorder Level]
    FROM [Product Table Query];
    So how would i change it
    2002 Mitsubishi Galant
    Progress: 90% [-▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓- ->

    Carputer Specs:
    Via M10K
    512mb Ram
    60GB HDD
    Souund Blaster Audigy2 NX
    OPUS ITX PC Case


  7. #7
    Variable Bitrate StrataG's Avatar
    Join Date
    Aug 2005
    Location
    New York
    Posts
    425
    Quote Originally Posted by Cheekz185
    this is the SQL I get....

    So how would i change it
    Just add a where clause.

    SELECT [Product Table Query].[Quantity on Hand], [Product Table Query].[Reorder Level]
    FROM [Product Table Query] WHERE [Product Table Query].[Quantity on Hand]<=[Product Table Query].[Reorder Level];

    That will only get you the two fields though. Likely you will want to add other fields to return, or all of them. To return all you would do:

    SELECT [Product Table Query].* FROM ................

  8. #8
    FLAC
    Join Date
    Apr 2005
    Location
    Queens, New York
    Posts
    1,386
    SELECT [Product Table].[Product Name], [Product Table].[Product Category], [Product Table].[Product Number], [Product Table].[Quantity on Hand], [Product Table].[Reorder Level]
    FROM [Product Table];



    what about this one.....
    2002 Mitsubishi Galant
    Progress: 90% [-▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓- ->

    Carputer Specs:
    Via M10K
    512mb Ram
    60GB HDD
    Souund Blaster Audigy2 NX
    OPUS ITX PC Case


  9. #9
    FLAC
    Join Date
    Apr 2005
    Location
    Queens, New York
    Posts
    1,386
    im sorry man how would i do it for this one??

    SELECT [Supplier Table].[Company Name], [Product Table].[Product Category], [Product Table].[Quantity on Hand], [Product Table].[Reorder Level]
    FROM [Product Table] INNER JOIN [Supplier Table] ON [Product Table].[Supplier Number]=[Supplier Table].[Supplier Number];
    2002 Mitsubishi Galant
    Progress: 90% [-▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓- ->

    Carputer Specs:
    Via M10K
    512mb Ram
    60GB HDD
    Souund Blaster Audigy2 NX
    OPUS ITX PC Case


  10. #10
    Variable Bitrate StrataG's Avatar
    Join Date
    Aug 2005
    Location
    New York
    Posts
    425
    Same exact addition as before, just swap [Product Table Query] for [Product Table]. This is really basic stuff, I just hope you aren't going to get tested on this. If you will be, you really need to read just the basics of SQL queries. A where clause should be in the basic intro to SQL.

Page 1 of 2 12 LastLast

Similar Threads

  1. Forum Slowness
    By Quattro in forum Mp3car Forum Suggestions/Comments
    Replies: 41
    Last Post: 10-28-2005, 06:50 PM
  2. Forum Really Slow
    By Quattro in forum Mp3car Forum Suggestions/Comments
    Replies: 58
    Last Post: 05-10-2005, 04:04 PM
  3. errors on the homepage
    By alti in forum Mp3car Forum Suggestions/Comments
    Replies: 61
    Last Post: 04-14-2005, 10:26 PM
  4. ID3 information into MS Access
    By NEO in forum Software & Software Development
    Replies: 16
    Last Post: 07-21-2004, 04:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •