Drop down list and report

  • Thread starter Thread starter meisaka
  • Start date Start date
M

meisaka

Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
 
Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
 
Thank you Duane.

The code you provide works.

More questions, now I want create 2 drop down menus, one is Borough and one
is address, when I select a Borough from the Borough menu the Address menu
will show only the addresses belong to that Borough.

After I selected the Boro and Address, I want to have the user info report,
how do I do it?

Thanks again.


Duane Hookom said:
Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
--
Duane Hookom
Microsoft Access MVP


meisaka said:
Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
Click to expand...
Click to expand...
 
Also, when I tried "Boro" with the similar code, it returned an error message
[Microsoft][ODBC SQL Server Driver]ORDER BY items must appear in the select
list if SELECT DISTINCT is specificed. (#145)

What am I missing? THanks again.

Duane Hookom said:
Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
--
Duane Hookom
Microsoft Access MVP


meisaka said:
Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
Click to expand...
Click to expand...
 
Go to www.rogersaccesslibrary.com and find his small sample database that
describes how to use Cascading Combo Boxes.

--
Duane Hookom
Microsoft Access MVP


meisaka said:
Thank you Duane.

The code you provide works.

More questions, now I want create 2 drop down menus, one is Borough and one
is address, when I select a Borough from the Borough menu the Address menu
will show only the addresses belong to that Borough.

After I selected the Boro and Address, I want to have the user info report,
how do I do it?

Thanks again.


Duane Hookom said:
Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
--
Duane Hookom
Microsoft Access MVP


meisaka said:
Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
If you want to order the records, try:
SELECT [address]
FROM [AllUser]
GROUP BY [address]
ORDER BY [address];

I don't know what you mean by "tried Boro".

--
Duane Hookom
Microsoft Access MVP


meisaka said:
Also, when I tried "Boro" with the similar code, it returned an error message
[Microsoft][ODBC SQL Server Driver]ORDER BY items must appear in the select
list if SELECT DISTINCT is specificed. (#145)

What am I missing? THanks again.

Duane Hookom said:
Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
--
Duane Hookom
Microsoft Access MVP


meisaka said:
Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Thanks again.

Duane Hookom said:
If you want to order the records, try:
SELECT [address]
FROM [AllUser]
GROUP BY [address]
ORDER BY [address];

I don't know what you mean by "tried Boro".

--
Duane Hookom
Microsoft Access MVP


meisaka said:
Also, when I tried "Boro" with the similar code, it returned an error message
[Microsoft][ODBC SQL Server Driver]ORDER BY items must appear in the select
list if SELECT DISTINCT is specificed. (#145)

What am I missing? THanks again.

Duane Hookom said:
Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
--
Duane Hookom
Microsoft Access MVP


:

Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top