CODEWITHSUNDEEP
mysqlformsms-access
Origin
Origin

Reputation: 47

MS Access: Using Single form to enter query parameters in MS access

compliment of the day. Based on the previous feedback received,

After creating a Ticket sales database in MS Access. I want to use a single form to Query the price of a particular ticket at a particular month and have the price displayed back in the form in a text field or label.

Below are sample tables and used query

CompanyTable

CompID    CompName
   A        Ann
   B        Bahn
   C        Can
   KK       Seven
   -        --

TicketTable

 TicketCode    TicketDes

 10            Two people
 11            Monthly
 12            Weekend
 14            Daily

TicketPriceTable

 ID    TicketCode    Price    ValidFrom

  1    10            $35.50    8/1/2010
  2    10            $38.50    8/1/2011
  3    11            $20.50    8/1/2010
  4    11            $25.00    11/1/2011
  5    12            $50.50    12/1/2010
  6    12            $60.50    1/1/2011
  7    14            $15.50    2/1/2010
  8    14            $19.00    3/1/2011
  9    10            $40.50    4/1/2012

Used query:

SELECT TicketPriceTable.Price
 FROM TicketPriceTable
 WHERE (((TicketPriceTable.ValidFrom)=[DATE01]) AND ((TicketPriceTable.TicketCode)=[TCODE01]));

Such as 'Month' field equals to input to [DATE01] parameter 'Ticket Code' equals to input for [TCODE01] parameter Textfield equals to output of the query result (Ticket price)

If any question, please don't hesitate to ask Thanks very much for your time and anticipated feedback.

Upvotes: 1

Views: 1289

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112402

Entering up to 300 different types of tickets

Answer to your comment referring to Accessing data from a ticket database, based on months in MS Access)

You can use Cartesian products to create a lot of records. If you select two tables in a query but do not join them, the result is a Cartesian product, which means that every record from one table is combined with every record from the other.

Let's add a new table called MonthTable

MonthNr    MonthName
    1      January
    2      February
    3      March
  ...      ...

Now if you combine this table containing 12 records with your TicketTable containing 4 records, you will get a result containing 48 records

SELECT M.MonthNr, M.MonthName, T.TicketCode, T.TicketDes
FROM MonthTable M, TicketTable T
ORDER BY M.MonthNr, T.TicketCode

You get something like this

MonthNr    MonthName   TicketCode  TicketDes
    1      January         10      Two people
    1      January         11      Monthly
    1      January         12      Weekend
    1      January         14      Daily
    2      February        10      Two people
    2      February        11      Monthly
    2      February        12      Weekend
    2      February        14      Daily
    3      March           10      Two people
    3      March           11      Monthly
    3      March           12      Weekend
    3      March           14      Daily
  ...      ...            ...      ...

You can also get the price actually valid for a ticket type like this

SELECT TicketCode, Price, ActualPeriod AS ValidFrom
FROM (SELECT TicketCode, MAX(ValidFrom) AS ActualPeriod
      FROM TicketPriceTable
      WHERE ValidFrom <= Date
      GROUP BY TicketCode) X
    INNER JOIN TicketPriceTable T
        ON X.TicketCode = T.TicketCode AND X.ActualPeriod=T.ValidFrom

The WHERE ValidFrom <= Date is in case that you entered future prices.

Here the subquery selects the actually valid period, i.e. the ValidFrom that applies for each TicketCode. If you find sub-selects a bit confusing, you can also store them as query in Access or as view in MySQL and base a subsequent query on them. This has the advantage that you can create them in the query designer.

Consider not creating all your 300 records physically, but just getting them dynamically from a Cartesian product.

I let you put all the pieces together now.

Upvotes: 0

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112402

In Access Forms you can set the RecordSource to be a query, not only a table. This can be either the name of a stored query or a SQL statement. This allows you to have controls bound to different tables through this query.

You can also place subforms on the main form that are bound to other tables than the main form.

You can also display the result of an expression in a TextBox by setting the ControlSource to an expression by starting with an equal sign

=DLookUp("Price", "TicketPriceTable", "TicketCode=" & Me!cboTicketCode.Value)

You can set the Format of a TextBox to MM\/yyyy or use the format function 

s = Format$(Now, "MM\/yyyy")

Upvotes: 0

Jan-Philipp Niewerth
Jan-Philipp Niewerth

Reputation: 161

You can refer to the values in the form fields by using expressions like: [Forms]![NameOfTheForm]![NameOfTheField]

Upvotes: 1

Related Questions