Combine Multiple Properties Lists

Apr 2, 2008

OK, not sure how to search for this so I am just going to ask. I have two properties that send me excel sheets with the name of the employee, employee number, department, and title. What I would like to do is make this into one list. So just one table that would list these fields and maybe get a new field that marks what property that person works at.
Example
Jon Smith, 12345, admin, office ( this is from one list)
Jane Smith 54321, admin , office (this from the other list)

What I want it to look like (query)

Name, Emp #, title,department,property
Jon Smith, 12345,admin,office, prop1
Jane Smith,54321,admin,office, prop2

How would i go about doing this?

View Replies


ADVERTISEMENT

Help With Multiple Lookup Lists

Nov 9, 2004

Apologies - i'm a "rookie" at Access - but i have searched for a while now on the site for the answer to my question... I'm sure there is a simple solution somwhere here but i cant find it!

I am putting togther an venue database - with all the usual fields: Name, Address, Capacity etc... of which i have set up YES/NO, Free Text and Number fields. No problem!

I have also set up various lookup lists which work fine, apart form the venue "Suitability" lookup list.

The choices are:

Corporate
Private
Drinks Reception
Dry Hire
Music Event
Conference

Now, some venues may be suitable for SEVERAL of these uses - so how do i select multiple uses from this list as opposed to just one?

Many thanks in advance for your help - im learning fast! :rolleyes:

View 2 Replies View Related

Reports :: Multiple Lists On A Single Subject

Jan 21, 2014

I have been asked to make a report from my database listing property sites and listed things found with them. Ex. a property might have 3 houses and 2 cars on it. Whenever I make a report the best I can make are groups that look like:

Property 1
House A
Car a
Car b
Car c
House B
Car a
Car b
Car c

I need something that could group car and houses directly to property 1 rather than cars grouped to houses grouped to property.

Ex.
Property 1
House A
House B
Car a
Car b
Car c

View 4 Replies View Related

Linking Records Between Multiple Lists - Client And Products For Finance Company?

Oct 8, 2012

I am trying to put together a database that tracks individual clients and what products they selected for a finance company. I have setup the CLIENTS list but because each client will have different (and multiple) services I wanted to add the SERVICES list as a separate item and then link each product to the individuals that each record relates to. There may also be two clients on the one product so again it would be ideal if the product (i.e a Home Loan product) was setup in the SERVICES list, and then I could link One or Two clients to that individual mortgage record using their data which is stored in the CLIENTS list.

View 2 Replies View Related

Combine Multiple Rows Into One Field?

Mar 28, 2008

Hello,

I am trying to find a way to combine multiple rows into one (field). I have 2 tables. One table is data the other table is notes. The notes table has a new record created for each new note. Both tables have the Account Number and and Sub Account Number. I am linking the two tables on these account numbers with a left join normally. There can be records in the data table and no records in the notes table. I guess what I want is a query or sql statement i can use in vba that will display all the data from the data table and all the notes from the notes table in on field.

This data is used in a large report. Right now I have the data table set to the report by itself and code on the report to pull the notes from the notes table using a ADO recordset with a do while that just adds each note to a single variable. This works fine and does the job. The only problem is the time it takes to run the report now. If the report has 1000 records with notes on 90% of them and each of those has multiple notes that recordset code has to run thu ALOT. I use to have all the notes just compile to one field in the data table and the report ran very fast that way. I had to change this since I need it this way as to filter notes by time and archive them after a period.

I have read thru so many posts and see lots of code functions to do this but its just the same thing I already have with the recordset code building the combined field. Any other way to do what I am trying? I think I have just been over thinking this stuff at this point an maybe a fresh set of eyes will help. Below are some simple table layouts for the 2.

**Account Number and Sub ID fields in both tables are not unquie.

Data Table: (this table has alot more data but this gives you the idea.)
Account_NUM - Sub_ID - Data1
1234 | 3 | 123 N Inc.
1234 | 3 | 666 DRN
4567 | 4 | 543 S Way.
7890 | 5 | zzz ABC

Notes Table: (Yes the notes field is a MEMO field)
Account_Num - Sub_ID - Notes
1234 | 3 | notes1
1234 | 3 | notes2
1234 | 3 | notes3


The output I am trying to Get:
Account_Num - Sub_ID - Data1 - Notes
1234 | 3 | 123 N Inc. | notes1 notes2 notes3
1234 | 3 | 666 DRN | notes1 notes2 notes3
4567 | 4 | 543 S Way. | NULL
7890 | 5 | zzz ABC | NULL

Thank you for any advice or thoughts.

Joe

View 2 Replies View Related

Trying To Combine Multiple Queries Into 1 Main Query

Jul 10, 2006

Can anyone help me with how I can accomplish this?

Here is what I currently have:

10 SQL Pass-through queries to update different Date field (Date1, Date2, Date3, etc.) based on the value of the next date field.

This is how each individual query is set up (there are 10 in total)
UPDATE tblApplications SET tblApplications.LastContact = [date1]
WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));


Here is what I would like to have: (as 1 query)

UPDATE tblApplications SET tblApplications.LastContact = [date1]
WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));

UPDATE tblApplications SET tblApplications.LastContact = [date2]
WHERE (((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Null) AND ((tblApplications.[Date Completed]) Is Null));

UPDATE tblApplications SET tblApplications.LastContact = [date3]
WHERE (((tblApplications.[Date Completed]) Is Null) AND ((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Not Null) AND ((tblApplications.Date4) Is Null));

Can I somehow combine them together like this or do I have to keep them as 10 seperate queries?

View 5 Replies View Related

Help W/ Query String- Combine Multiple Fields

Oct 3, 2007

Hey guys-
I have 2 tables- and need to find the record matches between them via their product code. Table 1 has the code as follows (and is considered 'the Master List')-
xxx-xx-xxx Where x is an integer.

Table 2 has split the 3 sections of the code out to 3 individual fields, like this-
Field1 Field2 Field3
xxx xx xxx

I need a query that will combine the 3 fields in table 2 to the same format as Table1 so that I can find the matches between them. I know this is a basic question, but I'm a learning newbie. How would I write that in a query? I don't think I want to write the format to the table- just include it in the query somehow...
Thanks!

View 6 Replies View Related

Combine Multiple Input Boxes Into One Field

Jun 18, 2006

Hey all,
I'm wondering if creating something like this is too difficult, or where I'd start? (see image attached). Here's kind of the process:
Scenario 1 :
1) user selects value from combobox
2) clicks text link ("add") to add value to table
3) value is displayed on form, with "remove" link that can take previously written data out of table
4) combo refreshes, moves down and process can repeat

The values would be added to the table seperated by commas.

Scenario 2:
This might be too difficult, in which case I could just use four or so text inputs and the user could type in the values... all written to different table cells. In this case, I'd need to show be able to write all the cells to a single textbox string on a form in the format "cast member 1, cast member 2" etc.

Anyone know what I'm trying to do and that could lend some advice? If there's an easier way to do this, please let me know. haha, thank yoU!

View 10 Replies View Related

Combine Multiple Criteria In A Single Dialog Box

Sep 29, 2005

Hi,
I wanted to know if it is possible to combine multiple criteria in a single dialog box. Let me explain:

I have a query for which two fields need criteria entered by the user. In design view under criteria I have entered this type of expession Code:[enter criteria] for both my fields. Of course when I run the query I first get prompted for the first criteria and then once I click OK I get prompted for the second...

What I would like is to have a single dialog box with both criteria on 2 lines...

Your help would be mcuh appreciated

View 3 Replies View Related

Combine Multiple Excel Sheets In Access

Sep 4, 2011

I have two reports that I import into Excel.They both show the locations in the warehouse where our product is supposed to be located. Over years of lack of maintenance they no longer match.What I would like to do is link these two sheets in Access so that the information can be updated as I progress and correct the information.Both have a common row or field called Locations. The problem is that some of the locations doesn't exist in both of the sheets. One of the sheets also has duplicated records for the same location.

I assume that I need some type of query to accomplish this; I have copied and pasted some samples of the information in the spreadsheets as well as the results that I am looking for.

View 14 Replies View Related

Combine / Concatenate Multiple Values In Different Records That Have Same ID

Mar 13, 2012

I have to concatenate the data in multiple records into one record. They have "skus" associated with them.

I have two columns.

ColumnSku: Which contains a product sku
ColumnModel: Which contains a model numbers

ColumnSku can contain the same sku hundreds of times
ColumnModel can contain the same model several times but not for the same sku

What I need to do is this: For every time a sku is shown in ColumnSku, take the model in ColumnModel and join them together separated by a comma.

For example

ColumnSku|ColumnModel
SKU1111|Model11111
SKU1111|Model22222
SKU1111|Model33333
SKU1111|Model44444
SKU1111|Model55555
SKU9999|ModelHHHHH
SKU9999|ModelJJJJJ
SKU9999|ModelMMMMM

Would end up like this
ColumnSku|ColumnModel
SKU1111|Model11111,Model22222,Model33333,Model44444,Model5 5555
SKU9999|ModelHHHHH,ModelJJJJJ,ModelMMMMM

How do I do this?

View 1 Replies View Related

Combine Multiple Tables With Some Identical Columns

Aug 22, 2014

The problem I'm having is that i need to combine 12 tables with multiple columns and not all columns have the same headers. So if a column is present it should be updated, if not it should be added.

The end result should be one column with all records and all columns that exists in all 12 tables.

The tables have an identifier that is the same for all columns (supplier reference).

Table 1 has 56 columns
Table 2 has 42 columns (of which some have the same header)
....

(--doing every column manually is something i would like to avoid if possible --)

View 5 Replies View Related

Table Column Name Properties/Combo Box Head Properties

Dec 8, 2006

Hi, I would like to change the properties of either the column heads in a combo-box or the column names for tables. I don't think there's any way to adjust the column head properties, and I'm doubtful there's a way to change the column caption properties.

Some of my column titles are long, and I'd like to be able to word wrap them essentially. Anyone know if this is possible? Thanks.

View 5 Replies View Related

Queries :: Query To Combine Different Fields From Multiple Tables?

May 13, 2014

I get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.

View 7 Replies View Related

Combine Multiple Tables (all Are With Unique ID Names) Into One Table?

Nov 22, 2013

I have create 6 tables for library books (which are differentiated by categories). Each table has different category and unique ID name e.g. F1, F2, F3..(for table 1), G1, G2, G3..(for table 2) and so on. How do I combine all those tables into 1 table for easy search for a book rather than open up each table? Tried append query but its ID run as 1, 2, 3... , not F1, F2, F3 and it only append 1 table, not the rest.

View 3 Replies View Related

Queries :: DLookup - Combine Multiple Criteria Syntax In Query

Apr 17, 2013

I am trying to create a Dlookup in Access 2010 within a query using query wizard. I want to lookup the tax rate for an employee based on a salary range and their 'tax category' (string). Through troubleshooting I can get the criteria to work separately.

These are: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'")

DLookUp("Base","TABWT",[grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")

These work and return the correct values for each column/row when I run the query.

However, when I combine the criteria (using the build wizard) as follows:

Expr1: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'" and [grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")

The Dlookup will returns 0 values but will not give an error message.

I've tried quite a few variations on syntax and quotes and so on. However, it's just not working for me.

View 3 Replies View Related

Combine Multiple Excel Sheets Into Single Access Database?

Jul 28, 2014

My goal in Excel was to combine them all into a single Pivot Table which I did. The issue is everytime I add a sheet I need to redo the Pivot Table, which would be a hassle as I will continually be adding sheets. I'm hoping that there is an easier way this could be done using Access. The individual sheets are emailed to me and then I put them into one Excel file, is there a way to take that Excel file with all the sheets and import it to Access and be able to sort the data as I would with a Pivot Table in Excel?

View 4 Replies View Related

General :: Form Control To Combine Multiple Field Values Into A Single Text Box?

May 10, 2013

I'm trying to set the control source for a control on my report that describes the number of portions that goes into a box. Sometimes there is a specific number of portions per box and sometimes it's a range. When the number of units is specific, then it is entered into the MasterCaseMinUnits Field. If there is a range of units possible than the min is entered into MasterCaseMinUnits and the max is entered into the MasterCaseMaxUnits.

I want my report to look at the mastercasemaxunits, if it is blank it will only display what is in the MasterCaseMinUnits field. If there is a value in the masterCasemaxUnits field, then it will display the masterCaseMinUnits & " - " & MasterCaseMaxUnits. this is the code I'm Using:

Code:

=IIf(Nz([MasterCasemaxUnits],[MasterCaseMinUnits])=[MasterCaseMinUnits],[MasterCaseMinUnits],[MasterCaseMinUnits] & " - " & [MasterCaseMaxUnits])

When I run my report, the control displays: #Type!

View 2 Replies View Related

Combine Multiple Records' Field Value Into One Field?

Nov 7, 2006

Let's say I have two tables:

Product.
Item Description
A It's Red
B It's Blue
C It's Green
D You'll love it

Category.
Item Category_name
A Cat1
A Cat2
A Cat3
B Cat1
B Cat4
C Cat1
C Cat6

I want to create a query/table that takes all the possible values for Category.Item and mix them into one field seperated by say a comma, so it would look like this:

ItemCategoryConsolodation.
Item Desc Categories
A It's Red Cat1,Cat2,Cat3
B It's Blue Cat1,Cat4
...


How does one do that?!

What's it called when you try to do this so I can Google it?

I've looked through like 8 Access books at the book store and none of them address anything like this. Can someone recommend a book that would cover "weird" stuff like this?

View 3 Replies View Related

Tables :: Combine Multiple Access Tables Into One

Mar 7, 2013

I have multiple tables that are created from some software that puts them each into the access database. each table is one year of data. the software cannot put all years into one table because of memory. I want to combine these tables into one. In addition, I need to do some manipulatins: the dates come over as MM-DD-YYYY strings and I need to convert them to dates MM/DD/YYYY, and also I need to parse a string to extract the General Ledger account number. I know how to use this easily from within Excel VBA using dateserial, and Instr etc., but now sure how/if I put some code into access and have it called to do this for me automatically. Each night the most recent year is automatically updated in access, so the combination file needs to be refreshed whenever the most recent year file changes.

I currently doing all of this in excel: first initialize the NewCombinedTable, then one by one dumping each year file into an excel worksheet, doing the work needed, then appending it to the NewCombinedTable. However, I think it would work much quicker if I was able to trigger the Combination to happen in Access along with the data manipulations.

I also need to know whether DAO or ADO is best for me to use with 2010...

View 1 Replies View Related

Saving Lists

Jan 23, 2008

Hello,

I have a main form where the user can click on a button that opens another form. In this form, the user can make selections from one listbox and they are copied to another listbox.
How can I save the contents of the listbox into a field that is part of the record on the main form? Can this be done?
I have a listbox on the main form where the selected items will be displayed and each record of the main form will have different values for the listbox.

Any ideas?

Thanks,
Mike

View 1 Replies View Related

Drop Down Lists

Apr 20, 2006

I am building a query for a university and the table i am working from has a column with every course run by the department.

How do i create a query that allows me to add a drop-down menu to the course instead of having to write out the course name everytime i want to query the number of pupils on a particular course.

View 1 Replies View Related

Lists Of Names

Sep 2, 2006

Hello all, I wonder if anyone can help me.

I have two queries which are both similar. They consist of a list of names with a ‘count’ function to see how many times that the name occurs. Some of the names in the two lists are identical and some are different.

I am looking to create a query (or smoothing) that takes the two sets of numbers, matches them by the name (NameID). If the name does not exist in one of the lists then put a 0 (see example below). Once I have this I then want to create a stacked bar graph to show the results.

Example:

Smith, Iain23 7
Bowman, Dan12 2
Rixon, Simon06
Cooper, Lee10
Jones, Andy21

I am very new to MS Access so please make any explanation descriptive.

Any help with is will be very grateful.

Harold Clements

View 3 Replies View Related

Compare Two Lists

Mar 5, 2007

I have two list's of names. I'm able to run a query to find the matches but the ones that don't match i also need to id?
For ex. List a has 200 names List b has 130 names, how can i find the 70 names that don't match?

View 4 Replies View Related

Combo Box Lists Everything

Jul 21, 2005

Ok! So I've binned the pivot table and gone for a continuos form option that allows the double click event that I'm after. A new problem has appeared.

On my main form I have 3 combo boxes (programme, product & company). When I click on any of the combo boxes i get a list of everything in the column it relates to. That works fine but it looks very cluttered. I'd like to have everything listed once only so that when i click on a programme it will filter the subform to show only companies working that programme.

Hopefully this little thingy I've put below will explain it a bit clearer

At the moment the programme combo box is displaying....

AAA
AAA
CCC
BBB
CCC
AAA
BBB

but i'd like it to display like this....

AAA
BBB
CCC

Every programme listed once only.

Any idea's folk's??? :confused:

View 6 Replies View Related

Combo Box Lists

Aug 5, 2006

I havea combo box on a form which looks up values from another table. The table containing the values which are looked up has another field where the data type is Yes/No. I want to limit the options in the combo box to just those which are set to Yes in the source table.

I know I need to use WHERE in the code for the combo box, but cannot find the correct syntax.

Please help!

Thanks,

Gary

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved