Newbie to Combo Box and Reports

Obviously in my last message I should have edited out ""Tom Wickerath"
wrote:". Sorry for missing that.

I have one last issue I need to find a solution for. The combo box shows
the PO number for every record. There are a large number of records in the
table the combo box draws from. Multiple records have the same PO number.

I need to find a way to search the table record by record and build up a
second table of unique PO numbers. The combo box then needs to draw from the
table of unique PO numbers.

Is there a simple way to build this second table of unique PO numbers? Or
is there another way to supply the combo box with the list of unique PO
numbers.

Thanks for all the help!

"JAJansenJr" wrote:

>
>
> "Tom Wickerath" wrote:
> Thanks to both Tom and Ken. I'll be trying your ideas out.
>
> I worked with a Microsoft support engineer who did not provide a complete
> solution but from what he suggested I then decided to use the Access help
> system and searched for "where condition" and
> then "Open Report Macro Action". This latter help page provided an example
> that matched my need and described the exact "Where Condition" clause I
> needed for the Open Report Macro. Everything now works perfectly.
>
> > Hi JA,
> >
> > > This is a great example to obtain information between two dates. What I want
> > > to do is to retrieve records containing the same Purchase Order number.
> >
> > You will use the same logic shown in the article, except that your situation
> > will be easier, since you are not querying between a range of values. In your
> > case, the combo box to select a City (ie. select one parameter) would be more
> > analogous to selecting (in an unbound combo box) or entering directly (in an
> > unbound text box) the purchase order number.
> >
> > > Another report I'd like to generate is to review all items ordered from a
> > > given manufacturer.
> >
> > Use the same idea. For a list of manufacturers, I'd probably use a combo box
> > with a RowSource based on a lookup table. That way, people could select the
> > manufacturer from a list, instead of having to spell the manufacturer's name
> > correctly in a text box (or fragment of a manufacturer's name, if you
> > concatenated the wildcard search character (*).
> >
> > > Another report that would be useful would be to generate a report showing
> > > all the records for a given item.
> >
> > This is very do-able, using the material I have supplied as a guide. It's
> > not intended to be an exact fit for any one purpose. Try creating the sample
> > from scratch, so that you can understand how it works. Then try applying the
> > same logic to your particular situation.
> >
> > Here are links to my QBF samples. if you are okay with using some Visual
> > Basic for Applications (VBA) code:
> >
> > http://www.accessmvp.com/TWickerath/downloads/elements.zip
> >
> > http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip
> >
> >
> > The first one shown above, Elements.zip, is about as easy as they get. Here,
> > I have shown just a single multi-select list box control, to allow one to
> > select one or more elements from the periodic table in the list. Hey, I'm a
> > chemist, so this example was natural for me! Anyway, you can make a
> > continuous selection by holding down the Shift key, or a discontinuous
> > selection by using the Control key.
> >
> > The Chap08QBF sample is the basis for all of my QBF forms. This sample came
> > from Scott Barker's book titled "Access 2000 Power Programming". It includes
> > the original form/subform, as well as my modifications to these two forms.
> >
> > If you'd like to learn how these work, I have a Word document and sample
> > Access database available here:
> >
> > http://www.seattleaccess.org/downloads.htm
> >
> > Look for:
> > Query By Form - Multi Select
> > Tom Wickerath, February 12, 2008
> >
> > If you are new to VBA code, then your first stop should be to check out
> > these two documents:
> >
> > http://www.seattleaccess.org/downloads.htm
> > DAO - Back To Basics Compilation/Demo
> > by Tom Wickerath, Jan/Feb 2007
> >
> > and
> >
> > Access Basics by Crystal
> > http://www.accessmvp.com/Strive4Peace/Index.htm
> >
> >
> > Tom Wickerath
> > Microsoft Access MVP
> > http://www.accessmvp.com/TWickerath/
> > http://www.access.qbuilt.com/html/expert_contributors.html
> > __________________________________________
> >
> > "JAJansenJr" wrote:
> >
> > > This is a great example to obtain information between two dates. What I want
> > > to do is to retrieve records containing the same Purchase Order number. The
> > > same purchase order number can cover several records as each record describes
> > > item(s) received from a given manufacturer. The reason for wanting a report
> > > of this kind is to review the items a given purchase order number covered.
> > >
> > > Another report I'd like to generate is to review all items ordered from a
> > > given manufacturer. This would be useful in planning for a future order,
> > > i.e. you would have some idea of what you were able to order from a given
> > > manufacturer previously.
> > >
> > > Another report that would be useful would be to generate a report showing
> > > all the records for a given item. This would also be useful in planning for
> > > a future order, i.e. you would have some idea if more than one manufacturer
> > > can supply a given item.
> > >
> > > I had no trouble creating a combo box to show all the purchase order numbers
> > > from the table. Where I'm stuck is knowing how to take the selected purchase
> > > order number and using this value as a key to generate a report containing
> > > all records containing this key.
> > >
> > > I'm searching both the Access 2007 help site as well as other tutorials
> > > located using a Google search, but so far haven't been able to find a way to
> > > do what I would like to do, as described in the foregoing.
> > >
> > > Thanks for your reply. If you have any other suggestions these will be
> > > appreciated and gratefully received!

Hi JA,

I certainly hope you didn't have to pay too much for that support engineer's
help. The newsgroups are free, and I'd like to believe that we provide high
quality support. (The samples I pointed you to all include the optional
WhereCondition statement in a DoCmd.OpenReport statement).

There are two ways of solving your last issue. The easiest way involves
adding one word to the Structured Query Language (SQL) statement that serves
as the rowsource for your combo box. This word would be: DISTINCT. If the row
source looks like this:

Select [TableName].[FieldName] from [TableName] order by [FieldName]

where TableName is the name of the applicable table, and FieldName is the
name of the field that contains your PO number. In that case, you can simply
add this keyword as follows:

Select DISTINCT [TableName].[FieldName] from [TableName] order by [FieldName]

If the row source is based on a saved query, then open the query in design
view. Then click on View | SQL View. Insert the DISTINCT keyword as indicated
above, just after the SELECT keyword. Save your changes, close the query, and
test.

However, while the above method is the easiest, it is *not* the most
efficient, because it will result in an automatic table scan. A table scan
means that all records must be read. If you have a network wire that
separates your PC from the data file, then you'll want to design efficiently
to avoid table scans. In that case, you very like would want to create a
lookup table, with the unique PO Number in the new table either set as a
primary key, or indexed uniquely. You would then join this field to your
existing PO Number field in a one-to-many relationship. So, what you need in
this case is a new query that serves as the source of data for a Make-Table
query. Something like this (as before):

Select DISTINCT [TableName].[FieldName] from [TableName] order by [FieldName]

You would then save this query as a temporary object (you can delete it when
you are done). Create a Make-Table query that uses this new query as a source
of data. See the following link for help on Action Queries, of which a
Make-Table query is one type.

http://office.microsoft.com/en-us/access/CH063653171033.aspx

You want the third item listed: "Create a table from another table with a
query". Alternatively, you could create a new table by from scratch, and then
use an Append query to add records to it. The Append query would use the same
temporary query that includes the DISTINCT (or a GROUP BY) clause as it's
source of data.

Note that a Make-Table query will not add any indexes; you'll need to open
the resulting table in design view and index the PO Number field
appropriately.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"JAJansenJr" wrote:

> Obviously in my last message I should have edited out ""Tom Wickerath"
> wrote:". Sorry for missing that.
>
> I have one last issue I need to find a solution for. The combo box shows
> the PO number for every record. There are a large number of records in the
> table the combo box draws from. Multiple records have the same PO number.
>
> I need to find a way to search the table record by record and build up a
> second table of unique PO numbers. The combo box then needs to draw from the
> table of unique PO numbers.
>
> Is there a simple way to build this second table of unique PO numbers? Or
> is there another way to supply the combo box with the list of unique PO
> numbers.
>
> Thanks for all the help!