Check quantity and value of used items

Question

Check quantity and value of used items

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a table called Modifiers :

This is the structure:

-----------------------------------------------------
|   ModId   |   ModText     |   AdditionalCost      |
-----------------------------------------------------
|     58    |   RANCH       |   2000                |
-----------------------------------------------------
|     86    |   GUACAMOLE   |   500                 |
-----------------------------------------------------
|     52    |   CEBOLLA     |   0                   |
-----------------------------------------------------
|     87    |   QUESO       |   150                 |
-----------------------------------------------------

I have another table called "Orders", whose structure is as follows:

---------------------------------------------
|   OrderId     |   OrderDateTime           |
---------------------------------------------
|   20001       |   2017-06-01 00:00:00     |
---------------------------------------------
|   20001       |   2017-06-01 00:00:00     |
---------------------------------------------
|   15224       |   2017-06-02 00:00:00     |
---------------------------------------------
|   13501       |   2017-06-03 00:00:00     |
---------------------------------------------

I have another table called "Transactions", whose structure is as follows:

---------------------------------------------------------------------
|   TransaccionID   |   Mod1ID      |   Mod2ID      |   Mod3ID      |
---------------------------------------------------------------------
|     1             |               |   86          |   87          |
---------------------------------------------------------------------
|     2             |   58          |               |               |
---------------------------------------------------------------------
|     3             |   87          |   52          |               |
---------------------------------------------------------------------
|     4             |               |   58          |               |
---------------------------------------------------------------------

The Transactions table has these characteristics:

Columns range from Mod1ID to Mod20ID .
As you can see in the example, the Ids in the Modifiers table are located in the different ModXID columns of Transactions . . li>
A Transaction can be composed of one or more modifiers.

What I'm looking for is to create a query in ms-access (which gets the expected results) , but I would like to know if there is a better way to get the same results "that is, the expected results":

NOTE: For short, this query only uses the Mod1ID columns, Mod2Id, Mod3ID and Mod4ID:

SELECT
    t.MenuModifierText AS [Modificador],
    COUNT(*) AS [Cantidad],
    (SELECT MenuModCost.AdditionalCost FROM MenuModifiers AS MenuModCost WHERE MenuModCost.MenuModifierID = t.MenuModifierID) AS ValorMod,
    (SELECT MenuModCost.AdditionalCost FROM MenuModifiers AS MenuModCost WHERE MenuModCost.MenuModifierID = t.MenuModifierID) * COUNT(*) AS [Valor]
FROM(
    SELECT MENUMODS.MenuModifierText, MENUMODS.MenuModifierID
    FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD
    ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS
    ON MENUMODS.MenuModifierID = ORDTRDS.Mod1ID
    WHERE ORDHRD.OrderDateTime BETWEEN #06/21/2017 00:00:00# AND #06/22/2017 23:59:59#
    UNION ALL
    SELECT MENUMODS.MenuModifierText, MENUMODS.MenuModifierID
    FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD
    ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS
    ON MENUMODS.MenuModifierID = ORDTRDS.Mod2ID
    WHERE ORDHRD.OrderDateTime BETWEEN #06/21/2017 00:00:00# AND #06/22/2017 23:59:59#
    UNION ALL
    SELECT MENUMODS.MenuModifierText, MENUMODS.MenuModifierID
    FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD
    ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS
    ON MENUMODS.MenuModifierID = ORDTRDS.Mod3ID
    WHERE ORDHRD.OrderDateTime BETWEEN #06/21/2017 00:00:00# AND #06/22/2017 23:59:59#
    UNION ALL
    SELECT MENUMODS.MenuModifierText, MENUMODS.MenuModifierID
    FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD
    ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS
    ON MENUMODS.MenuModifierID = ORDTRDS.Mod4ID
    WHERE ORDHRD.OrderDateTime BETWEEN #06/21/2017 00:00:00# AND #06/22/2017 23:59:59#
) AS t
GROUP BY t.MenuModifierID, t.MenuModifierText;

sql ms-access

asked by Mauricio Arias Olave 22.06.2017 в 01:18

source

2 answers

Problem in the header (HTTP Header Manager) Jmeter with ":" Is it possible to send an email, which contains buttons in the body, and with these buttons can be processed actions and how? in ASP.NET

score 0 · Answer 1

That query is correct.

Access works differently from the database languages with the ANSI SQL standard (SQL Server, Oracle, etc.), therefore a simple Group By makes it somewhat complex, in my experience when I use Access I do it through the Wizard since in doing it manual sometimes the result is not the expected one, besides that the syntax is to a certain extent tedious.

Properly Access is an RDBMS, not a database, hence the difference in syntax and the way it works.

score 0 · Answer 2

I think the structure is very poorly planned, and as little as you can I advise you to modify it.

I would use the [Transactions] table with only two fields: [TransactionID] and [ModID], and so you could create as many modifiers as you want for 1 transaction.

Imagine how simple it would be to get the number of modifiers:

Select ModID, Count(*) From [Transacciones] GROUP BY ModID

To this query we would simply have to join with the order tables to filter dates, and with the table of modifiers to get the name of each ModID

Although there is a possibility that you can not modify the structure. In that case, I would choose another way to do it. However, some programming is needed.

I'll add some programmed idea that I have in mind. But KEEP IN MIND that I have programmed without going through the vba validator, so it can have all kinds of errors. Even so, if you have programming notions, I think you will understand the concept well, which is what I am most interested in:

1) We created a temporary table with the information that I put before: Name of the table: [TEMPORARY Transactions] Field1: TransactionID Field2: ModID

2) We program a small method. Something like that: Public Sub Generate Transactions (ByVal Date1 As Date, ByVal Date2 As Date) CurrentDb.Execute "DELETE * FROM [TransactionsTEMP]"

    For I = 1 To 20
        CurrentDb.Execute "INSERT INTO [TransaccionesTEMP](TransaccionID, ModID)" & _
            " SELECT ORDTRDS.OrderID, MENUMODS.MenuModifierID " & _
            " FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD " & _
            " ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS " & _
            " ON MENUMODS.MenuModifierID = ORDTRDS.Mod" & I & "ID " & _
            " WHERE ORDHRD.OrderDateTime BETWEEN #" & Format(Fecha1, "mm/dd/yy") & "# AND #" & Format(Fecha2, "mm/dd/yy") & "#"
    Next I

    'Aquí ya tendrías la tabla que te comenté al principio. Con ella ahora se pueden hacer consultas mas elegantes, ya sea por código o usando consultas
End Sub

It could happen that the volume of data is so large that the data load is slower than the query. In that case, you could generate the query from code, which does not solve that cumbersome SQL query, but it does make it easier to create code, which avoids copying / pasting the query again and again:

Public Sub GenerarConsultaTransacciones(ByVal Fecha1 As Date, ByVal Fecha2 As Date)
    Dim ConsultaSQL As String

    ConsultaSQL = _
        " SELECT t.MenuModifierText AS [Modificador], COUNT(*) AS [Cantidad]," & _
        "(SELECT MenuModCost.AdditionalCost FROM MenuModifiers AS MenuModCost WHERE MenuModCost.MenuModifierID = t.MenuModifierID) AS ValorMod," & _
        "(SELECT MenuModCost.AdditionalCost FROM MenuModifiers AS MenuModCost WHERE MenuModCost.MenuModifierID = t.MenuModifierID) * COUNT(*) AS [Valor]" & _
        "FROM("

    For I = 1 To 20
        ConsultaSQL = ConsultaSQL & _
            " INSERT INTO [TransaccionesTEMP](TransaccionID, ModID)" & _
            " SELECT MENUMODS.MenuModifierText, MENUMODS.MenuModifierID " & _
            " FROM (OrderTransactions AS ORDTRDS INNER JOIN OrderHeaders AS ORDHRD " & _
            " ON ORDTRDS.OrderID = ORDHRD.OrderID) INNER JOIN MenuModifiers AS MENUMODS " & _
            " ON MENUMODS.MenuModifierID = ORDTRDS.Mod" & I & "ID " & _
            " WHERE ORDHRD.OrderDateTime BETWEEN #" & Format(Fecha1, "mm/dd/yy") & "# AND #" & Format(Fecha2, "mm/dd/yy") & "#"
        If I < 20 Then ConsultaSQL = ConsultaSQL & " UNION ALL "
    Next I

    ConsultaSQL = ConsultaSQL & ") AS t GROUP BY t.MenuModifierID, t.MenuModifierText"

    On Error Resume Next
    DoCmd.DeleteObject acQuery, "Consulta TRansacciones"
    On Error GoTo 0
    CurrentDb.CreateQueryDef "Consulta Transacciones", ConsultaSQL
End Sub