Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59

    Arrow Unanswered: Queries

    Hi all
    I have a database that lists stock and each stock type has a unique InternalID like the following:

    D
    DM
    M
    MS
    MP
    N
    KA
    KP
    HD
    PR
    S
    CD
    G

    The codes were set for the stock before the database was built so they cant be changed.

    I have a count query that is based on the results of a query that searches for a specific stock type and weather or not its been sold, the problem Im having is that when I query the db for all stock with the internal ID of D I get the DM stock in the query also. Ive been using:

    SELECT [tbl_stock/equipment].EquipID, [tbl_stock/equipment].InternalID, [tbl_stock/equipment].Sold
    FROM [tbl_stock/equipment]
    WHERE ((([tbl_stock/equipment].InternalID) Like "D***") AND (([tbl_stock/equipment].Sold)=0));

    How can I just get the D code and not the DM code in the query results?

    Thanks

    Mike

    DB Attached
    Attached Files Attached Files
    Last edited by Icerat; 08-02-04 at 09:42. Reason: Solved original problem

  2. #2
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59

    Lightbulb

    Hi
    I Solved original problem, silly mistake on part, so i edited post (couldnt figure out how to delete post) with a new problem,

    Any ideas guys

    Mike

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Alright, you say the codes were in place before the database was built, so you can't change the primary keys...

    I think you can...

    What if you were to create a new table called Categories or what have you, then put all of your different categories in this table with a numeric key. Now you have a list of numeric values and a prefix for each code. Then create a anew field on your main table and use your new key to define a relationship to categories. Then remove the letters from the beginning of your part numbers. This sounds like a lot of work, but I swear to god it's worth it.




    Alternately you could use an "AND" operator...
    WHERE (([tbl_stock/equipment].InternalID Like "D*" AND NOT [tbl_stock/equipment].InternalID like "DM*") AND (([tbl_stock/equipment].Sold)=0));
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59

    Many Thanks

    Hi
    Used:
    WHERE (([tbl_stock/equipment].InternalID Like "D*" AND NOT [tbl_stock/equipment].InternalID like "DM*") AND (([tbl_stock/equipment].Sold)=0));

    In the end, thought it would envolve using AND NOT buy couldnt figure out how to get it to work, im pretty new to SQL.

    Thanks again
    Mike

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Glad to help!!

    I will strongly suggest one last time that you restructure your schema though. It will make EVERYTHING so much easier for you.

    What happens when you need to generate mass sales and marketing forcasts? How about cross referencing different categories? How do you avoid bad data from alphanumeric keys? How would you add a description for each category?

    The list goes on my friend...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Hi
    Any suggestions you can give would be greatly appreciated, as I new to access Ive learnt a lot and Im getting there, slowly. Any suggestions on how it can be improved, layout/design/etc/etc would be appreciated like I said,

    Thanks again

    Mike

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    A good direction to move in would be creating a table for categories and defining a one to many relationship between categories and stock.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    would you be able to provide me with a sample to work with as it took me long enough to get this far

    Thanks
    Mike

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'd need to know more about what the database is expected to do in order to provide a solid example.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    What I need it to do is the following:

    A client comes in and wants to by a computer / component,

    I log their order in the db and then once the machine is finished I put in the order details section the components used.

    From these details I can mark the items as sold so that when the next order goes in I can see that, that particular component has been sold.

    I need to be able to generate invoices for any given order and to be able to see what each employee has sold what (Like when you click on the orders form and the details appear in the bottom).

    I need to be able to add stock as and when it comes in (I work for a charity and we receive donations all the time).

    I thought I had achieved this with my design, be it a little inefficient, but if it can be done better Ill have a look.

    Mike

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would say go with six tables: invoice, invoice_detail, category, part, order, OrderDetail

    The tables would be of the following format:

    Category
    ---------
    category_id (PK)
    prefix
    description

    Part
    ----------
    part_id (PK)
    category_id (FK)
    description
    cost?

    Invoice
    ------------
    invoice_id (PK)
    invoice_date

    InvoiceDetail
    --------------
    invoice_detail_id (PK)
    invoice_id (FK)
    part_id (FK)

    Order
    --------------
    order_id (PK)
    order_date

    OrderDetail
    ---------------
    order_detail_id (PK)
    order_id (FK)
    part_id (FK)
    cost? (you may notice I listed price in two places, this is to account for current retail, and retail at the time of sale. I don't know if you even need to track cost, but these are the two places I would do it)

    The basic breakdown is, you track your incoming inventory using the "Invoice" tables. You track outgoing or sold items using the "Order" tables. You keep tabs on your part categories and types using the "Part" tables.

    This affords you all sorts of wacky flexibility if you ever need it, as well as making reporting a snap.

    Want to know your current inventory on a given part? Take the total number of parts you received in the invoiceDetail table and subtract the total sold from the OrderDetail table.
    Last edited by Teddy; 08-02-04 at 13:35.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Could you provide me with a sample db with the relationships defined?

    Would my forms i created work with this?

    Would i get the same sort of functionality with this setup?

    Thanks

    Mike

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Icerat
    Could you provide me with a sample db with the relationships defined?
    No, unfortunately I cannot provide access files to anyone outside my company. I went through that "conflict of interest" fiasco once, I'm not doing it again.

    Would my forms i created work with this?
    I don't know what your forms look like, but I can assure you this layout can do anything your current layout does. Naturally you'd have to do a little redesigning. It comes down to whether you want to do a little work now and give yourself a solid platofrm to work from, or make a smattering of quick-fix hacks to try and make what you currently have work, resulting in major headaches in the future (some of which you are already experiencing).

    Would i get the same sort of functionality with this setup?
    The same functionality and much, much more.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Hi
    ok i'll give it a try myself, would you be able to explain the relationships (where to join etc etc) as this is something im still getting to grips with, once i got that sorted i should (i hope) be able to build the rest upon it.

    Thanks

    Mike

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    once you get the tables layed out, just match up the field names in the relationship designer.

    Category and part are related by category_id

    invoice and InvoiceDetail are related by invoice_id
    InvoiceDetail and Part are related by part_id

    Order and OrderDetail are related by order_id
    OrderDetail and part are related by part_id
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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