user to add two fields of his choice to the query

[commodityintelligence]

Greetings,

I am merging a series of different tables into one query to export decision-making information. I have some architecture issues I need to ask for help on. I have no programming training.

I will explain my structure before asking my questions.

I have data from a table (updated monthly by adding the most current month’s data) that is evaluated by certain arithmetic, given an adjective to describe it, and then being exported to the user via e-mail.

The goal is for the user to input dateperiods such that they are “JAN07” or “FEB07”, following a syntax of <3 letter abbreviation for month>(last two digits in year).

Currently, there are RECORDS with data like:

Record Name PART NUMBER JAN07 FEB07 MAR07
RECORD1 abc 123 155 132
RECORD2 def 235 254 244
RECORD3 fgh 323 355 389

(there’s other fields of data, but it is not involved in any of the calculations as always tied to the PART NUMBER)

Record Name is dependent on the part number’s characteristic. For example, if part number abc and fgh, is painted with RED, then RECORD1 and RECORD3 may have the same (RED)

PART NUMBER will never be duplicated. It is unique.

JAN07, FEB07, and MAR07 is tied to the Record Name. If RECORD 1 and RECORD 3 were the same, then the JAN07, FEB07, and MAR07 values would be identical.

The Record Name comes from a table that stores all the RecordNames and JAN07, FEB07, MAR07 fields. The PART NUMBER comes from a USER IMPORTED file.

It is easy for me to calculate (FEB07 – JAN07) / JAN07 in a query, but that is if it is defined by the user in the query itself.

I want to use a switchboard that allows the user to say, “I want to see the % change from MAR07 and JAN07” or “I want to see the change from MAR07 and FEB07”.

I’ve tried to do the [] in the field name box of a query, but it requires me to add a table. When I do that and run the query, it brings up a popup box, and then I put in JAN07 and it runs the query results, which for that expression column is simply JAN07 for every record.

How do I set It up such that my user can decide via switchboard / macro / popup box what 2 dates they want to calculate % change for?

I see 2 issues:

#1. A way for the user to add two fields of his choice to the query (JAN07, FEB07, MAR07)

#2. A way to change my expression field that does the growth calculation based on the fields chosen by #1

Thank you for your time.
I was rather pressed for time but here is the logic and partial code solution. If you have any further questions, feel free to ask. But first!

1. Create 2 Combo Boxes on a Form (cboFirstDate and cboSecondDate).
2. Set the RowSourceType of both Combo Boxes to Value List.
3. Set the RowSource of both Combo Boxes to "JAN07";"FEB07" ;"MAR07";..."DE C07"
4. You will be selecting your MMMYY Ranges from these Combos and building an SQL String from which you will create a Query.
5. The code assumes your Table Name is tblYourTable, but replace it with the appropriate Name.
6. The code below will create and execute a User Defined Query based on your selections in the Combo Boxes.
7. This should definately point you in the right direction.
8. As previously stated, if you are still stuck, either myself or one of the other Moderators/Experts will assist you. Good luck.

On Error Resume Next
Dim MySQL As String, qryDef As QueryDef
 
 'Make sure both Combo Boxes have entries in them
If Not IsNull(Me![cboFirstDate]) And Not IsNull(Me![cboSecondDate]) Then
  'Make sure both Combo Boxes are not equal
  If Me![cboFirstDate] = Me![cboSecondDate] Then
    MsgBox "1st and 2nd Date selections cannot be equal", vbExclamation, "Invalid Entries"
      Exit Sub
  Else
    'If you get here, you are good to go! DELETE existing Query if present
    CurrentDb.QueryDefs.Delete "qryYourQuery"
 
    'Build SQL from the 2 combinations of MMMYY
    MySQL = "SELECT [" & Me!cboFirstDate & "], [" & Me!cboSecondDate & "] FROM tblYourTable;"
 
    'Create the Query, Refresh the DB Window, then execute it
    Set qryDef = CurrentDb.CreateQueryDef("qryYourQuery", MySQL)
 
    Application.RefreshDatabaseWindow
 
    DoCmd.OpenQuery "qryYourQuery"
 
  End If
End If
 
Set qryDef = Nothing

[nico5038]

You made it a daunting task :-)
With the present unnormalized approach I woudl just construct the query dynamically like:

dim qd as DAO.querydef

set qd = currentdb.query defs("qryDynami c")
qd.SQL = "SELECT ID, (" & me!combo2 & " - " & me!combo1 & ") / " & me!combo1 " as Percentage from tblX"

' here the query is constructed and can be used e.g. in a docmd.transfers preadsheet command to get an excel sheet that can be mailed.

Getting the idea ?

Nic;o)