Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Question Unanswered: Newbie Question Multi Value Fields and Parameter Queries

    I have two tables Customers and States. A customer can live in more than one state in my use case. I have a relationship that between tblCustomer [State] which is a multi-value look up field. I have a query that I want to be parameter driven by state. I want to see all my customers who live in two states. For example AK and FL. I created a Parameter called [CustomerStates] I have chosen both tables and added the fields from Customer [FirstName] Customer [LastName] and the tblState with Field [State]. Under [State] I have listed my parameter that I created in the Parameter wizard. I am unable to select AK,FL
    Results display only customers where AK comes first. Now I can Select FL and the customer shows up or AK and the same customer shows up. I am in Design View. What am I missing besides experience?? Thanks in advance. Jim

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    First of all, don't use lookups in tables. It's messy and hard to maintain. Access will enforce a link between tables from the Relationships window without lookups.

    Second, don't have multi-value fields. They're a big no-no in relational database design, in part because they make it impossible for this kind of query to work.

    For this kind of relationship, you need a third table, one that will sit between Customers and States - say, CustStates. This table will need at least two fields - a link to the primary key of Customers, and a link to the primary key of States. These two fields can form the primary key of CustStates. (You can add effectivity dates as well, but that starts to get complicated.)

    Now, to find out which customers are in two selected states, the following query works:
    Code:
    SELECT c.Name
    FROM States AS s
    INNER JOIN (Customers AS c INNER JOIN CustStates AS cs ON c.CustID = cs.CustID)
    ON s.StateID = cs.StateID
    WHERE
    s.State=[Enter second State]
    AND
    c.CustID IN
    (select cscs.CustID
    from CustStates as cscs
    inner join states as ss
    on cscs.StateID = ss.StateID
    where ss.State = [Enter first State]);
    It's not pretty, but it will return the required information. I'm sure that some of the forum veterans will be able to improve on it.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Smile

    Weejas,

    Thanks so much. I saw the warning in the "Missing Manual" about using multi-values. So there it is and you have validated it. It just seemed an "quick and easy" fix to multiple locations for a customer and then trying to find out what customers had more than one location.

    Now as for deletes, I have an orders table that yep, you guessed it has look ups to other tables for order details and order status. I am trying to do a delete of an example record using a delete query. But I keep getting a data lock message.
    I have added in Design View (still working on my SQL skills)....See attachment
    please...I know I am boning it up somehow....I think I am really trying to do the nasty dangerous cascading deletes. I have one record of a sale 10/1/2010 that I want to get rid of.

    I appreciate your help and will not incessantly bug you.

    PS: What is the area below...Send Trackbacks?
    Attached Thumbnails Attached Thumbnails CustomerOrderDelete.png   Relationships.png  

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    From which table are you trying to delete order information? If you have enabled cascade deletes, you should be able to simply delete the order header information, and the order details will be wiped out accordingly. However, this is very risky, and increases your chances of data loss! I would be more likely to write a VBA sub that prompted for an order ID, validated it against business rules (e.g. status, has it been invoiced, etc) and then delete the details and then the header. Better yet, have a status of "Deleted", and exclude that status from useful queries and reports. You can then worry about archiving old orders later.

    ***EDIT***
    Just seen the other problem. Your delete statement includes the OrderStatus table. Remove this! You don't need to delete the status just because you're deleting the order. This will probably be the cause of the record lock error.
    ***END EDIT***

    You're welcome for the help! I spent a while chasing red herrings into blind alleys, so I'm always happy to share what I've learned.

    I have no idea what the track backs are...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Deletes

    I followed your advice and got the error "Could Not Delete From Specified Tables"

    This is a practice db as you might have guessed.

    I am trying to delete a record from tblOrders where the sales were before 10/2/2010 (my daughter's name is in that one order) and the order was for 3 products. I can easily send you the db if you like, in fact I will attach it. So you don't spend all day guessing at my floundering. The query is in the Order Details Section called qryDeleteOldSales. It is in a zip file. If you have time....

    PS: I like your reference to magic, do you practice? I studied under John Carney and Jason Leeb in the US. Those guys are pure genius with close up. I do some close up with coins, cards and other fun stuff.

    Jim
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Sadly, I'm still stuck with Office 2000, so I will not be able to open your database files.

    Looking at your delete query, I think that you could replace it with the following (untested) air code:
    Code:
    DELETE *
    FROM
    Orders 
    WHERE
    Orders.OrderDate = [Enter order date to delete]
    AND
    Orders.OrderStatus = (select OrderStatus.ID from OrderStatus where OrderStatus.Status = 'Shipped')
    AND
    Orders.OrderID = [Enter order number to delete];
    If you have enabled cascade deletes, this will purge the order header that meets your criteria, and then purge all related records from OrderDetails as well.

    I don't practice magic, although I admire its execution. At a Christmas Party, we were treated to some up close magicians, one of whom replaced a deck of cards with a perspex block while it was in my hand!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Talking

    Weejas,

    I tried your solution, turned on cascading updates and deletes. It did not work for me, but that is ok. It is about trial and error. However, I did the "smart thing" backed up my db and called it Chocolatestore for Deletes so no harm no foul.

    In the mean time, I monkeyed around with the SQL based on your model and came up with this:

    DELETE Orders.DatePlaced, Orders.StatusID, Orders.CustomerID
    FROM Orders
    WHERE (((Orders.DatePlaced)=[<#10/02/2010]));

    It accomplished what I needed but it formed a Parameter based query as it asked me for what date I wanted to delete the record. I see that Angela Marie is still a customer in the tblCustomers however in the tbl.Orders I see the expression #Deleted in the fields impacted.

    I turned on cascade update / deletes in the relationships.

    The good news is it did not delete any other records from Products, Categories, Order Status or Customers.

    I am NOT asking you to do my work for me...just a pointer in the right direction on how to think about this.

    Thanks, Jim
    PS: I am stationed in Afghanistan, if you want a souvenir from here let me know and I will gladly send you something for your time and effort. I also realize you are NOT my personal tutor and you do this out of the love of the trade / art. Jim

    Can you help me with a WHERE clause in SQL where I can delete

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If cascade deletes didn't do what you wanted, what did it do? Was there an error message?

    The square brackets around an expression denote a parameter in Access. This allows for queries to prompt for user input each time that they are run, rather than having to get the designers to change them. I would advise that a delete query have parameters, unless you plan to use a combination of calculated and static values (e.g. OrderDate <= (Now() - 120) AND OrderStatus = 3 (where 3 is the StatusID of 'Shipped')).

    Cascade deletes flow from the one side to the many side. Therefore, if you enable them on the relationship between orders and order details, deleting order records will delete dependant detail records, but not the related customer or product records.

    Let me know again what defines the record(s) that you want to delete, and the table(s) to be affected, and I'll see what I can do. However, as I mentioned before, I'm stuck with Office 2000, and so I do know all of the capabilities of the later versions.

    Thank you for the offer, but no souvenir is necessary! I'm happy to help someone putting himself in harm's way - it's the least I can do.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Deletes * 2

    Good Morning and thanks for your kind reply.

    I guess what I am looking for is a parameter driven delete. I have a pic attached that I created giving you the SQL View and Design View of the Query. I also have a pic of the tables. As you can see I entered an order for Lisa Maier that is in 2009.

    I am looking for a delete that will allow me to select records older than X date.
    ******NEXT*****QUESTION*****
    I also may have one more question about look ups. I corrected the states look up in the customer table as it links to a table called States and returns the two digit State Text.

    I am also working on a status look up for OrderStatus in a Customer Table, the field is set to Number in design view.(same as States). I have a Table called OrderStatus that has a primary Key and a Field Called Status (text). I clicked the look up tab at the bottom of Customer table / OrderStatus field, selected Combo Box (just like in the States example) and formulated the query in the same fashion as States but it only displays the Order Status Number instead of Ship to Factory or Received. Bound Column is 1 from Status and the Column Count is 2 I have looked at both DBs and the structure of the query seems the same. I am missing one minor tweak..somewhere..thoughts?

    See attachments as you have time

    Jim
    Attached Thumbnails Attached Thumbnails OrderStatusSelection2.png   Deletes.png  

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Today has been a bit of a nightmare at work, but I'll have a look tonight when I get home.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Thumbs up

    I am just so appreciative, no worries. You are the best. I am moving forward with Crosstabs etc. What is your first name? Thank you again.
    Jim

  12. #12
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Smile

    Also if you are too tired tonight, no worries.

  13. #13
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Regarding Look Up Presenting Text Values / SOLVED

    I am an idiot! I should have more attention to detail and remember proper syntax, is the key.

    I looked again at the expression that I sent you in the build for looking up the values of one field from another and realized in the example that I was having an issue with, Orders.OrderStatusID (number) looking to OrderStatus.Orderstatus From OrderStatus ORDER BY OrderStatus.[OrderStatus] is the right way. it now displays the "Text" value in column 2 (Shipped or Send To Factory) instead of 1 or 2.

    I learned that the previous statement that I sent you I had too many [] brackets. I bracketed everything. Not needed as I discovered.

    Thanks and hope you are having a great day at work.

    Jim

  14. #14
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    []'s are only needed when your field, or table name is more than one word, separated with a space (" ").

    So for example:

    Code:
    >CORRECT<  tableName.[A Field Name]
     >WRONG<   tableName.A Field Name

    The same also applies to table names, so:

    Code:
    >CORRECT<  [A Table Name].[A Field Name]
     >WRONG<   A Table Name.[A Field Name]
     >WRONG<   A Table Name.A Field Name

    It is generally considered best practice (for ease of reading), to always follow the syntax:

    Code:
    tableName.[fieldName]
    Even if the field name is not two words. This just makes clear to someone reading, exactly what table it is, and what field it is. Especially, in a case such as yours, where you have a field name named the same as the table name that it is contained within.
    Looking for the perfect beer...

  15. #15
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Angry Look Ups

    Thanks Kez....I appreciate your help. Betwen you and Weejas...great support.

    PS: Question How is it that you guys are posting your example code in the posts and it looks like it is in a form? Different from the message.

Posting Permissions

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