Combining Fields Form Different Tables Into The Same Column On A Listbox

Mar 15, 2007

Hi Everyone,

I hope I'm posting in the right place, I've been trying to solve my problem using queries so I thought it might be appropriate here.
I have a database for a health care service which contains among others the following four tables...

Table 1 contains client details, primary key [ClientID] plus other client details.
Table 2 contains Episode of care details, primary key [EpisodeofcareID], [episodeofcareDate] etc...
Table 3 contains all test results for assessment 1, primary key [CoreID], [CoreDate] plus numerous scores for individual tests.
Table 4 contains all test results for assessment 2, primary key [HonosID], [HonosDate] plus numerous scores for individual tests.

Table 1 is linked to Table 2, and Table 2 is linked to both Table 3 & 4.

I have set up a search procedure whereby the user enters a clients name which then opens a list box of all clients with that name. When the client selects from the listbox I want a second listbox to open which has three visible columns. One giving the date of each episode of care for the client, the second giving the date of each assessment completed and the third giving the name of the assessment carried out.

I'm managing to get the date of the Episode of Care to display on the first visible column but I'm getting stuck on the next two. This would seem to involve somehow combining data from different tables into individual columns on the listbox. For example I need the second visible column on this listbox to list all the dates of assessments for the chosen client from tables 3 & 4. (e.g. [CoreDate] and [HonosDate]). Column three would then give the title of the assessment next to the date in column two. None of the tables have a field listing titles as this is determined by which table the data is entered into. I've been trying to solve this by queries without much success.

Could anyone give some ideas as to how I could solve these problems?

Many Thanks

John

View Replies


ADVERTISEMENT

Combining Multiple Columns Into One Longer Column (without Combining Fields)

Jul 17, 2013

I trying to combine three columns that I have into one column without combining fields.

Currently what I have:
(see image below)

What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc

The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.

View 14 Replies View Related

Combining Address Fields Into One Column

Sep 12, 2004

Hello,
How can I combine two seperate fields into one field? For example
field1 has 123 field2 has Smith St. I want to put it in one column
that has 123 Smith St.

Thanks

View 1 Replies View Related

Tables :: Combining Fields To Create Unique ID

Nov 14, 2013

How i would best combine values in a table to produce a 'primary key id number.'

For example: the first letter of a city in the ID and the next number available/auto number - Portsmouth -> P233

I know i can create this in a query however i want it as the unique ID for that record entry in a table. If that doesn't make sense i can try to elaborate some more.

View 14 Replies View Related

Tables :: Combining Fields In Different Tables

May 29, 2014

I'm creating a database which would automatically assign a unique workorder number in the "WorkorderNumber" field of the "Workorder" table. Note: this will not be the Primary number for the work order.The WorkorderNumber will be developed by combining fields from the "System" table. Fields used to create the WorkorderNumber from the "System" table are:

1) Location [currently in the field is "MAX"]
2) CalendarYear [currently in the field is "2014"]
3) NextWorkorderNumber [currently in the field is "1"]

I need the following to happen to the "WorkorderNumber" field of the "Workorder" table:

a. I need the field to read as followed: Max-2014-00001
b. I need the number 00001 to autonumber to 00002 on the next entry of a new request. [MAX-2014-00002].
c. I need to be able to control how the "WorkorderNumber" field populates by changing the "CalendarYear" and "NextWorkorderNumber" fields within the "System" table without messing up prior workorder numbers already populated.

View 1 Replies View Related

Combining Fields On A Form

Dec 20, 2006

Hay Folks,
I'm having trouble with a subform. My subform (dataform) has a lot of fields. I want to reduce them by combining some fields with an expression. This would save some space on the form.
The problem.
I cant get the expression right to show real values instead of key-values.
Here's the sample expression for the control-field:
=[Roadtype] & [Rnumb] & "-" & [position] & " " & [from] & "-" & [to] & " " & [lanetype] & [letter]

Most fields are of the lookup type, which have a rowsource-expression to show the desired value. I.e. for [lanetype]...
SELECT [qryBPSverhardebaansoort].[lanetype], [qryBPSverhardebaansoort].[Omschrijving] FROM qryBPSverhardebaansoort;

Can anybody help me on track?
Thanks a lot.

View 10 Replies View Related

Tables :: Sum All Fields In Tables Column And Put Sum Result Into Variable?

May 29, 2015

i would like to sum all the column fields in a tables column where an id is the same as the id in that table.
I know you can use the SUM function in the select statement of your query.

But how can i set this sum result into a variable, so i can use it for calculation further on in the program?

I work with Access 2010

View 4 Replies View Related

General :: Listbox Not Showing Correct Column On A Form

Dec 17, 2012

I have a listbox on a form that looks up a table to to allow me to pick a stock location. The table only has 2 colums in it, the Primary Key Field (ID) and the Store location. When using this in a a form instead of getting the Stores Location, all I get is the ID. I have tried changing the Bound Volumn Value and the Column Count Fields, however no change.

View 2 Replies View Related

Combining Data From The Same Column In A Query

Feb 10, 2008

I need to combine data from a Yes/No field such that for any instance of a Yes/True the query will show Yes (checked). Only if there are no instances of Yes should the query return No (unchecked) for the field. In addition, this rule must apply for each individual account number in a [separate] table of accounts.

Tables: (only relevant fields shown)
SavingsClub - AccountNumber (PK), CustomerID, FlagAccountClosed, FlagDelete
SavingsClubAccounts - Account, Reservation, Redeemed (PK=another field)
SavingsClubReservations - Reservation (PK), FlagSpecialOrder

The following query works fine except that it returns two entries for John Doe if his account has entries for both FlagSpecialOrder=True and FlagSpecialOrder=False. You can see in the attached image that accounts 8 and 9 both have two entries each. Because these customers have a special order I want just one one row for each showing Yes (checked).

I want the query to return a single row for each unredeemed account. If there are no special orders then that field will be No (unchecked), but if there is at least one instance of a special order the field show be Yes (checked).

(To put this into some kind of context, customers can place orders which may be stock items or special orders, in which case I need to record the supplier, quotes, delivery dates, etc. In some cases they save for an item and there may or may not be a special order involved. Once the order is fulfilled it is redeemed.)

SELECT DISTINCT Customers.Name, SavingsClub.AccountNumber, SavingsClubReservations.FlagSpecialOrder,
Suppliers.ShortName, SavingsClub.FlagAccountClosed, SavingsClub.FlagDelete
FROM Suppliers
INNER JOIN (OrderDetails
INNER JOIN (((Customers
INNER JOIN SavingsClub ON Customers.Index = SavingsClub.CustomerID)
INNER JOIN SavingsClubAccounts ON SavingsClub.AccountNumber = SavingsClubAccounts.Account)
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation)
ON OrderDetails.Index = SavingsClubReservations.OrderID)
ON Suppliers.Index = OrderDetails.Supplier
WHERE (SavingsClubAccounts.Redeemed=False)
ORDER BY Customers.Name

I've spent a week trying to solve this and thought I was on the right tack with the following query, which does return a single instance of each relevant account, but I can't manage to combine it into the query above to obtain the complete query.

SELECT DISTINCT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=False) And (SavingsClubAccounts.Redeemed=False)
And SavingsClubAccounts.Account Not IN
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False))
UNION
(SELECT SavingsClubAccounts.Account FROM SavingsClubAccounts
INNER JOIN SavingsClubReservations ON SavingsClubAccounts.Reservation = SavingsClubReservations.Reservation
WHERE (SavingsClubReservations.FlagSpecialOrder=True) And (SavingsClubAccounts.Redeemed=False));

Appreciative of any help, otherwise I think I'll end up manipilating the data in code and use a temp table, etc...

View 2 Replies View Related

Combining 2 Query Columns Into 1 Column With Data In Separate Rows

Mar 29, 2012

I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.

The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.

I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.

Current:

ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0

Needed:

ID/ Hours / Type
101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg

I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.

Access 2007, Windows 7.

View 5 Replies View Related

Combining Fields

Oct 12, 2007

Hello,

I need to find a way to modify the following structure:

Name, Page, Grid
Acton Rd, G10, 12
Acton Rd, G10, 4
Acton Rd, G10, 8
Adams Ct, F6, 2

Into something like this:

Name Page Grid
Acton Rd, G10, '4, 8, 12'
Adams Ct, F6, '2'

What is the best way to go about tackling this?? I need to create this in a table so that I can export it from Access and into a .dbf to be used with another program.

Thanks,

View 3 Replies View Related

Forms :: Displaying Selected Fields In The Same Column In Same Form?

Jul 10, 2014

How you could display a few selected fields in different rows within the same table.

SO as an example

Machine_No Board_No Board_Mode Board_Data
1 1 xxx xxxx
1 2 yyy ssss
1 3 ttt wwww
2 1 323 dddd
2 2 eee qqqq
2 3 rrr pppp

Based on which Machine_No i pick, i would like to display it's Board_Mode and Board_Data for all Board_No values (1,2,3)

i will be able to display this on the same form. (say machine_no = 1)

Board_Mode Board_data
1 xxx xxxx
2 yyy ssss
3 ttt wwww

View 6 Replies View Related

Combining 2 Fields Into 1 Without Spaces

Jan 26, 2006

I have 2 seperate fields within a select query (In this example Field 1 - Country and Field 2 Number).

I have combined the 2 together into one field.

However a space appears within my results i.e


Field 1 Country Field 2 - Number
----------------- -------------------
England 4
USA 10

Combined fields:

Field 3 - Combined Country + Number
--------------------------------------------

England 4
USA 10

How can I remove this space within Field 3


Thanks

View 4 Replies View Related

Combining Fields For Display

Aug 15, 2006

I have fields lastname, firstname, spousefirstname, spouselastname -

I would like to display:

Koenig, Mark & Jane (not a problem)

- but in some cases the spouselastname is different so then I would like to display:

Koenig, Mark & Jane Doeinger

This is how it displays with my code:

Koenig, Mark & Jane & Doeniger

I can't seem to get rid of the & between the spousefirstname and spouselastname.

Code:SELECT Potentials.Agent, [LastName] & ", " & [FirstName] & IIf([SpouseFirstName] Is Null Or [SpouseFirstName]=""," "," & " & [SpouseFirstName]) & IIf([SpouseLastName] Is Null Or [SpouseLastName]=""," "," & " & [SpouseLastName]) AS Expr1, Potentials.Address, Potentials.City, Potentials.State, Potentials.ZipFROM Potentials;

How do I get rid of the second &

View 5 Replies View Related

Modules & VBA :: How To Use Form Fields To Fill Numerous Records Under Same Column

Feb 2, 2015

I am using .FormFields to fill a quotation template in Word, but the subform I am retrieving the data from has rows of records under each column and I need to send more than just the first row to fill the required bookmarks in Word.

The code I'm using is

Set doc = appWord.Documents.Open("S:TemplatesQuotation Template - Test.doc", , True)
With doc
.FormFields("FirstName").Result = Me!FirstName
.FormFields("FirstName2").Result = Me!FirstName
.FormFields("LastName").Result = Me!LastName
.FormFields("LastName2").Result = Me!LastName

[Code] ......

View 4 Replies View Related

Queries :: How To Distribute Different Values Of One Column In New Column Fields

Jan 30, 2014

In my table for duplicate "line no" I have different "contractor" like below.

LINE NO CONTRACTOR

L-0001 C-1000
L-0001 C-2000
L-0003 C-6000
L-0003 C-8000
L-0003 C-9000
L-0004 C-5000

Now I would like to make a query for transposing values like below:

LINE NO CONTRACTOR1 CONTRACTOR2 CONTRACTOR3

L-0001 C-1000 C-2000
L-0003 C-6000 C-8000 C-9000
L-0004 C-5000

how I have to make this query?

View 1 Replies View Related

Help Please.. How To Update One Column In Many Tables From One Form

Jul 26, 2007

I was just assigned this database task couple weeeks ago, I know VERY LITTLE. I have all my tables and forms set up. My company builds custom cars....very few per year. I have dozens of tables for all the different componetns of the car....and each table has the same column "Unit" in it. I have a form that when a new car is began being built, the the first thing it does is ask the user to type in the Unit for the new car, but that only puts it in the one table i referenced when creating this form. I want/need this Unit information to be placed in every single table (20+) that has the column "Unit" in it. How can i do this, any information is greatly helped.....im getting so frusturated, i feel like i've learned a lot in the past 2 weeks since starting but i stil really know hardly anything.

View 3 Replies View Related

Combining Fields To Filter For Same Addresses

Sep 17, 2005

Hello
I have a political database with a separate field for street number, street name and apartment number. I've been asked to prepare a query that will only list the first member of a household. Example, the query would only show one member from a two person household if they both have the same address. I figured that I would somehow prepare a query that combined these 3 fields in a expression and then display the total row and choose FIRST. That didn't work, perhaps my syntax was off. Here's what I tried:

Expression: (Trim(StrConv([AddressStreetNumber]&" "&[AddressAptNumber]&" "[AddressStreetName],3)

Then I set the Total line to FIRST

Is it my syntax or is it that I can't join separate fields together like you can in a report ?

View 2 Replies View Related

Combining Multiple Like Fields And Totaling

Nov 10, 2006

Not exactly sure if a query is what I need in this situation or if it is what I need how to get there.

In the attached db example on the case form
there is a section for technicians to go in and take credit for steps that they performed as part of the overall case

So clv1 might be done by User A
Then clv2 might be done by User B

But the next case it might be switched.

I need a method getting the sum of the total clv's field for each technician in two different ways
1 would be the total clvs for USer A for the current month,
2nd would be the total for the year- or actually a prompt for a date range

Tried using the query wizard but it doesnt combine the names
Then I tried an individual query on each set - that worked but then I only get the ones in the first column - not all the clvs that they did.

Hope that makes sense - is there a way to do this or am I in the wrong forum for trying to figure out a way?

Well the db example is imb so it cannot be attached

Thanks for any help.

View 13 Replies View Related

Combining Multiple Fields Into One Colomn

Feb 15, 2007

Hello,

I have a table with employee numbers in four fields (Leader, Facilitator, ect...). How can I combine those numbers into one column trough a query?

Thanks in advance.

Keith

View 1 Replies View Related

Combining 3 Fields Space Problem

Sep 15, 2007

Hi,

Im combining 3 fiels with the following code.

Newfield: [Field1] & " " & [Field2] & " "[Field3]. So after eacht field a space is placed but when field 2 had no data there are 2 spaces between field 1 and 2. How do i solve this

You can react in euther english or dutch

Tank you in advance

View 1 Replies View Related

Combining First Letters Of Two Text Fields

Feb 21, 2005

In a form I can easily combine and display the content of two text fields in a third text field with the formula =[field1] & [field2]. Very well. Now I would like to combine and display only the first letter of each of the two text fields. This I would expect to go somewhat like this =(letter,1,1[field1]) & (letter,1,1[field2]). How is the correct formula?

View 4 Replies View Related

Combining Fields In A Search Query

Jan 5, 2005

Hi,

I was trying to combine some fields into one with a search query,using sql. I was going to search for a postcode then add the first two lines of an address (which are at present in 2 fields) together seperated by commas ",". For instance:

Strd
12 Rock View
Str
Marston Cresent

change to :

12 Rock View, Marston Cresent

I can add fields together with an expression but cannot divide them by commas, any help appreciated.

M-.

View 6 Replies View Related

Reports :: Combining Same Values From Two Fields

Aug 21, 2013

I would like to make a report to show how many employees and which employees are attending to which colleges/universities.

In my data (800 records), I have two fields which is "College 1" and "College 2" for each employee.

There are values that are enter in college 1 for some employees, other employees have the same value that is enter in in college 2. How do I get to show a report that has all the employees who attended the same college in either college 1 or 2?

For instance, this is my raw data:

Name - College 1 - College 2
Bob - University of HI - Honolulu CC
Sandy - Honolulu CC - University of HI
Clare - Kapiolani CC - University of HI
John - University of HI - Windardward CC

I want my report to show:

Colleges -
University of HI
Bob
Sandy
Clare
John

Honolulu CC
Bob
Sandy

Kapiolani CC
Clare

Windard CC
John

View 4 Replies View Related

Queries :: Combining Fields In A Query

Jan 21, 2014

I know how to concatenate fields in a query but have not done it with memos before. I have three memo fields and I want each to show up in one field with a bullet in front of each. Memo1 may be empty and memo2 and memo3 may have something or memo 3 may have some text but the others are empty so I need to be able to list the memos without the empty spaces. Is a query the best place to do this or in the report and how do I do it?

View 4 Replies View Related

MS Access Combining Fields Into A Longer One

Jul 27, 2014

i wish to combine multiple fields (there are no fixed number of fields, they vary depending on the data, so i guess union queries are out of the question) into one large field. For example:

TABLE 1:
PNumber
PName
C1
C2
C3

1
AAA
0.1
0.2
0.3

2
BBB
0.4
0.5
0.6

So i wish to combine the fields C1, C2 and C3 into a larger fields containing all the data. So considering the example above, it should look like this:

TABLE 2:
PNumber
PName
C1+C2+C3

1
AAA
0.1

1
AAA
0.2

1
AAA
0.3

2
BBB
0.4

2
BBB
0.5

2
BBB
0.6

I plan on entering data into TABLE 1 using a form and running a query, or some code etc so that it looks like TABLE 2.

View 14 Replies View Related







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