combo box that pciks values from SQL query

[nickgoldie]

I just created a sql query that pulls several spreadsheet into a single access query.

SELECT Sheet1.*
FROM Sheet1 UNION SELECT Sheet2.* FROM Sheet2;

Questions 1: How do I export these records into a table?

Questions 2: How do i create a combo box that pulls specific records from that table?

I need to create a combo box that brings up all records for the selected value.


For example, if i select department 1 i want to see all records from the table on a single page. There are multiple records for department 1.

Any help would be greatly appreciated.

[June7]

Are you linking to an Excel workbook? Are you creating a new table or adding records to existing table?

What do you mean by 'a combo box that pulls specific records'? Combobox is a data entry control. Are you asking how to create combobox drop list?

Have you built forms?

[nickgoldie]

i got the data into one table.
And the key to the data is department number.
I created the combo box and the form.
However, i have multiple lines for department 1 in the table.
When I select department 1 in the combo box i want to see all associated records for that department. Right now i get one record per page. And i have total of 17 pages.
I have two departments. I want the user to click on department number in the combo box and pull records associated with the department on a single page.

I will attach a sample later on.

[nickgoldie]

Please find the attached file.
My user needs to select a value from the combo box and all fields from the all data combined query should be displayed.
For example, when the user selects 29 he should see all 16 records.

Question 1: how do i create a table from query named "all data combined"
Questions 2: do i need to create a primary key in that new table?
Question 3: How do i create a combo box so that when i select department 19 i pulls all 16 records form the newly created table on one page.

thank in advance!

[June7]

Can't test because linked spreadsheets not provided.

1. Use 'Make Table' query.

2. You can use autonumber field.

3. Create a form with filter. Here is tutorial for one way http://datapigtechnologies.com/flash...tomfilter.html
Set up your form as Continuous view and arrange controls to look like table. Put the unbound search combobox in form Header section.

[nickgoldie]

the query that i created has only a SQL view and i don't know how to create a table out of it. When i go to SQL view and press make table it creates a blank table.

i used the following:SELECT finance.*
FROM finance UNION SELECT nik.* FROM nik;

when i click on make table it creates a blank table.

[nickgoldie]

i actually created another query from this union query and now i have the master table.

i also created the key and the form.

now i need to be able to filter the form. my form is in tabular mode.see attached.

[June7]

I don't see need for Form1.

The embedded macro on All does not filter, it sets focus on the record selected in the combobox. Is this the desired behavior?

If you want to actually filter the data than follow the tutorial in referenced link.

The combobox RowSource would be: SELECT DISTINCT ID FROM All;

The form's RecordSource would be: SELECT * FROM All WHERE ID= Forms!All!Combo11

I use only VBA, no macros. In combobox AfterUpdate property select [Event Procedure]. Double click the ellipses (...) to go to the VBA procedure and type code: Me.Requery
This might be accomplished with macro, just never tried it.

[nickgoldie]

thanks to you i actually figured it out.

CAn you answer one more question for me?

I created a report out of my form, but i want to have each department printed on its own page. For example, department 29 should have 1 page, dep 4 should have 2 pages.

basically, how to insert a page break depending on the change of the department number?

[June7]

Try using the report Grouping & Sorting to create a group header on the department field. Put the department ID and name info in the header. Set the group header Force New Page property to 'before section'. That works for me in one of my reports.

[nickgoldie]

that worked for me as well.

As you can see i am using a lot of external spreadsheets that are linked to access. However, when i enter new rows of data in the spreadsheets i need to change the range that is imported in access, which is fine. However, i want to create a query that adds only the new record to my "all" table. I just do not understand how to add additional records form my spreadsheets to my existing main table.

[nickgoldie]

i forgot to do the attachment.

i added one new record to excel spredsheet 4.
Now i want only the new record to be added to the ALL table(without making any changes to the old records in the all table, because i entered comments in the comment field.)

If i run the create table query all my data from the Comments field is erased. and i need it. How to insert just the new entry form excel sheet 4 to the existing All table?

[June7]

You need to do an APPEND sql action of records not already in All. What would be unique identifying criteria that could be used to determine record not already in All? This unique criteria could be a single field or combination of fields.

[nickgoldie]

the title field will be unique. How do i write the append query given unique title field? find unmatched query wizard?

[June7]

Last example from here http://www.techonthenet.com/sql/exists.php may be useful.