Parameter or Filtering

[Nanette]

I'm going to be designing a report that needs to pull data depending on three
specified fields that the user chooses.

I'd like help on how to do this correctly.

Thanks

 

[Klatuu]

Where are these fields?

 

[Nanette]

The fields are:

Table: RFQ, Field: RFQType (Choices are R or C)
Table: RFQ, Field: RFQNo (Many choices)
Table: Item, Field: ItemNo (Many choices)

 

[Klatuu]

Okay Nanette, you will need to post some more info.
How will the user select the values for the fields, for example?
Are you using a form?
From where do you run the report?

 

[Nanette]

I was thinking that I should create a form that would have those three fields
to choose from. These would be combo boxes. Once all three choices were
picked then another form would appear with many of the fields automatically
filled in. Using a form would allow the user to make changes if necessary.

There would be a command button to run/print a report that would be on the
form.

 

[Klatuu]

I suggest you use one form with the three combos as unbound controls and the
other controls bound to the fields in the form's recordset for editing. A
couple of questions:
I notice the Item is in a different table. Is the Item also in the RFQ table?
Will the form's record source by the RFQ table or a query?
Will the user have to select an item from each combo or can they make no
selections and return all records or just select from one combo and return
the records matching that one combo?
Do you expect to return multiple records or are you trying to find a
specific record?

Once I know the answers to the above questions, I can give you some examples
that will help.

 

[Nanette]

Hi Klatuu,

After thinking about this more (and talking to the boss) I believe that I'm
going to need to use a query to create this form. This keeps getting more
complicated. I'm kind of new to this so any help will be greatly appreciated.
I also think I need to better explain what I'm thinking of, so...

From a form/query I need the user to input an RFQ #. This should give him a
listing of RFQ #'s that match. Now the person should see another screen (or
same screen with different options) that lists the selected RFQ #'s with
another option box to choose from the multiple Change #'s that belong to the
specified RFQ. After selecting the Change #, a new screen is seen that
contains all the RFQ #'s with the matching Change #'s. Now another option
appears so that the person can choose from a list of Line Item #'s. After
the Line Item #'s are chosen, I need to populate a form with these three
fields and populate several other fields. The data for populating the form
comes from the LineItem table, a BOE table, and a Pricing matrix table.

Within the process above, I also need the system to check the RFQType field
to determine if the return data rows have an "R" or "C". (The data will
always be either an "R" or "C".) This RFQType (R or C) refers to a pricing
matrix. If the RFQ Type is R, certain fields of the predefined form need to
be populated with specific numbers. Of course, if the RFQ Type is C, the same
fields of the predefined form need to be populated with specific numbers.

Tables are:
Table: RFQ, Field: RFQNo (Many choices) PK
Table: RFQ, Field: RFQType (Choices are R or C)

Table: LineItem, Field: ChangeNo (Many Choices), Maybe a 3 PK primary key?
Table: LineItem, Field: RFQNo (Many Choices), Maybe a 3 PK primary key?
Table: LineItem, Field: LineItemNo (Many Choices), Maybe a 3 PK primary key?

Table: BOE, Fields: Many fields (didn't think you needed to know all these)

Table: PriceMatrix, Many fields (didn't think you needed to know all these)

 

[Nanette]

Hi again Klatuu,

I'm going to repost this to a new question under the Forms Design section
with the title of Major Form Design. I hope you will be able to find it and
help out some more. I understood your comments easily. Thought I'd get more
responses if the question was under something more current and appropriate.