none
Query DLookUp RRS feed

  • Question

  • Good Morning,

    I have an issue with a query where I'm trying to lookup a value from a table but can't seem to get the criteria correct. I have attached screenshots with some notes if someone is able to steer me in the right direction.

    Any help would be appreciated as I am very new to this.

    Thanks


    DeanGarber

    Wednesday, December 6, 2017 4:34 PM

Answers

  • Okay, try:

    SELECT InventoryTable.[Date], 
        InventoryTable.Scan,
        Games.Game_Number,
        Games.Game_Name,
        InventoryTable.[Slot Number],
        InventoryTable.[Ticket Value],
        InventoryTable.[Tickets in Book],
        InventoryTable.[Book Value],
        Games.Ticket_Value,
        Games.Tickets_In_Book,
        Games.Game_Value,
        InventoryTable.[POS Number Sold]*Games.Ticket_Value AS Sold,
        InventoryTable.[Ending Ticket Number]-Games.Tickets_In_Book AS TicketsRemaining,
        Games.Ticket_Value*(InventoryTable.[Ending Ticket Number]-Games.Tickets_In_Book) AS CurrentInventory
     FROM InventoryTable
     INNER JOIN Games
     ON InventoryTable.Scan Like Games.Game_Number & "*"
    
    

    Hope it helps...

    • Marked as answer by DeanGarber Thursday, December 7, 2017 8:15 PM
    Thursday, December 7, 2017 7:01 PM

All replies

  • Hi DeanGarber,

    Can you also please post the SQL statement for your query? Thanks.

    Wednesday, December 6, 2017 5:18 PM
  • Game_Number in Games table looks like a number.

    Game Number in InventoryTable Query looks like TEXT, probably because =Left([Scan],3) returns TEXT since Scan is probably TEXT.

    Please display your InventoryForm.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Wednesday, December 6, 2017 5:28 PM
  • I had considered that but double checked the formats. The ideal situation for me would be to save the values in the table so we can create a few different queries but.....

    DeanGarber



    • Edited by DeanGarber Wednesday, December 6, 2017 9:04 PM
    Wednesday, December 6, 2017 8:55 PM

  • DeanGarber

    Wednesday, December 6, 2017 9:08 PM
  • Hi DeanGarber,

    Thanks for posting the SQL statement for your query. You said when you open this query you are prompted for "Game_Number" with underscore (_); however, the SQL statement does not have "Game_Number." I see a "Game Number" without an underscore.

    Also, your previous screenshot of the query result shows a "Scan" column. However, I don't see a Scan field in the SQL statement.

    Lastly, you said the Game Number is a result of a Left() function, such as Left(Scan,3). Again, I don't see a Left() expression in the SQL statement.

    So, are you sure this is the correct SQL statement for the query that is giving you a problem and prompts for a parameter? If so, I can't see how.

    You might consider sharing a copy of your db with dummy data to help us see the whole picture better.

    Just my 2 cents...

    Wednesday, December 6, 2017 9:18 PM
  • My bad. Wrong Query.

    DeanGarber

    Wednesday, December 6, 2017 11:26 PM
  • Hi DeanGarber,

    That was better. Thanks.

    I am not sure if the DLookup() is necessary because you are joining the InventoryTable with the Games table anyway. However, you are using Games.Game_Number = InventoryTable.[Game Number]. Without seeing your table structure, I am thinking this may not be correct. Perhaps you meant to say: InventoryTable.Scan Like Games.Game_Number & "*" in your JOIN. For example, what do you get with this simple query?

    SELECT InventoryDate.[Date], 
       InventoryTable.Scan,
       Games.Game_Number,
       Games.Game_Name,
       InventoryTable.[Slot Number],
       InventoryTable.[Ticket Value],
       InventoryTable.[Tickets in Book],
       InventoryTable.[Book Value]
    FROM InventoryTable
    INNER JOIN Games
    ON InventoryTable.Scan Like Games.Game_Number & "*"
    Just my 2 cents...
    Thursday, December 7, 2017 2:12 AM
  • Hello DeanGarber,

    Could you share us screenshots of InventoryTable? Including current data and table structure?

    Did the InventoryTable have a field "Game Number"? If has, what's the value?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 7, 2017 8:29 AM

  • DeanGarber

    Thursday, December 7, 2017 1:41 PM
  • Screenshots of the 2 tables above. The ultimate idea here is to get [Game Name], [Ticket Value], [Tickets In Book], [Game Value] to populate the query based on [Game Number]. Once I get [Game Name] to populate I can figure the other 3 fields out from there.

    Each row in the Games table is static which means that [Game Name], [Ticket Value], [Tickets In Book], [Game Value] never deviate from their particular [Game Number]


    DeanGarber

    Thursday, December 7, 2017 1:53 PM
  • Hi Dean,

    Did you try the query I posted earlier? What was the result?

    Thursday, December 7, 2017 2:18 PM
  • I did. It asked for the date when I went to open it. See screenshots!

    DeanGarber

    Thursday, December 7, 2017 2:28 PM
  • Hi Dean,

    Thanks for the update. Let's forget about the Date issue for now. Did you get the matching game name with the correct Game Number or Scan? If so, then you should be able to adapt what I gave you to your situation, so you don't need to use Left() or DLookup().

    Regarding the Date issue, it was a typo on my part. For some reason, I called the table InventoryDate rather than InventoryTable. So to fix it, just change InventoryDate.[Date] to InventoryTable.[Date].

    Hope it helps...


    • Edited by .theDBguy Thursday, December 7, 2017 3:36 PM
    Thursday, December 7, 2017 3:36 PM
  • Excellent. That worked great! THANK YOU! I am trying to add the other fields,  [Ticket Value], [Tickets In Book], [Game Value] to the SQL but am doing something wrong? Any ideas?


    DeanGarber


    • Edited by DeanGarber Thursday, December 7, 2017 4:09 PM
    Thursday, December 7, 2017 4:09 PM
  • Hi,

    Looks like you have two FROM clauses. Try taking out the last one.

    Hope it helps...

    Thursday, December 7, 2017 4:14 PM
  • This is the current SQL. My thought was to add ON InventoryTable.Scan Like Games.Ticket_Value & "*" 
    after ON InventoryTable.Scan Like Games.Game_Number & "*" BUT that doesn't work either. I guess I'm not sure how to add that?

    SELECT InventoryTable.[Date], 
       InventoryTable.Scan,
       Games.Game_Number,
       Games.Game_Name,
       InventoryTable.[Slot Number],
       InventoryTable.[Ticket Value],
       InventoryTable.[Tickets in Book],
       InventoryTable.[Book Value]
    FROM InventoryTable
    INNER JOIN Games
    ON InventoryTable.Scan Like Games.Game_Number & "*"


    DeanGarber

    Thursday, December 7, 2017 4:50 PM
  • Hi Dean,

    Rather than modify the JOIN clause, have you tried simply adding more Fields? For example:

    SELECT InventoryTable.[Date], InventoryTable.Scan, Games.Game_Number, Games.Game_Name, InventoryTable.[Slot Number], InventoryTable.[Ticket Value], InventoryTable.[Tickets in Book], InventoryTable.[Book Value], Games.Ticket_Value, Games.Tickets_In_Book, Games.Game_Value FROM InventoryTable INNER JOIN Games ON InventoryTable.Scan Like Games.Game_Number & "*"

    Hope it helps...

    Thursday, December 7, 2017 5:00 PM
  • Beautiful! THANK YOU!!

    Last thing I hope? I noticed that you cant open this Query in normal design mode and I need to add some calculated fields such as the following and since they have values from 2 different tables I'm confused on how to do that?

    $ Sold=[POS Number Sold]*[Ticket_Value] 

    Tickets Remaining In Book=[Ending Ticket Number]-[Tickets_In_Book]

    Curent Ticket Inventory=[Tickets Remaining In Book]*[Ticket_Value]


    DeanGarber

    Thursday, December 7, 2017 6:39 PM
  • Hi Dean,

    Are you saying two more tables other than the ones already in the query? If so, which table in the current query is related to them?

    Thursday, December 7, 2017 6:47 PM
  • No its the same two tables 

    DeanGarber

    Thursday, December 7, 2017 6:50 PM
  • Good. That should be simple enough. Can you give me an example of the calculations you need to add? Thanks.
    Thursday, December 7, 2017 6:50 PM
  • $ Sold=[POS Number Sold]*[Ticket_Value] 

    Tickets Remaining In Book=[Ending Ticket Number]-[Tickets_In_Book]

    Curent Ticket Inventory=[Tickets Remaining In Book]*[Ticket_Value]


    DeanGarber

    Thursday, December 7, 2017 6:54 PM
  • Okay, try:

    SELECT InventoryTable.[Date], 
        InventoryTable.Scan,
        Games.Game_Number,
        Games.Game_Name,
        InventoryTable.[Slot Number],
        InventoryTable.[Ticket Value],
        InventoryTable.[Tickets in Book],
        InventoryTable.[Book Value],
        Games.Ticket_Value,
        Games.Tickets_In_Book,
        Games.Game_Value,
        InventoryTable.[POS Number Sold]*Games.Ticket_Value AS Sold,
        InventoryTable.[Ending Ticket Number]-Games.Tickets_In_Book AS TicketsRemaining,
        Games.Ticket_Value*(InventoryTable.[Ending Ticket Number]-Games.Tickets_In_Book) AS CurrentInventory
     FROM InventoryTable
     INNER JOIN Games
     ON InventoryTable.Scan Like Games.Game_Number & "*"
    
    

    Hope it helps...

    • Marked as answer by DeanGarber Thursday, December 7, 2017 8:15 PM
    Thursday, December 7, 2017 7:01 PM
  • Ok I got it plus some of the other things I wanted to add.

    Thanks a bunch for going out of your way to help.

    Very much appreciated!!!!!


    DeanGarber

    Thursday, December 7, 2017 8:14 PM
  • Hi,

    You're welcome! Glad we could assist. Good luck with your project.

    Thursday, December 7, 2017 8:18 PM