I have a table which shows when a product is available in the stores.
For example.
Product Store Available
A NS Jan, 2013
A BC Marc, 2013
How can I combine the available/store field which just shows
Product Available:
A - Jan, 2013 in NS AND Mar, 2013 in BC.
i I have two queries.. What i'm hoping is to combine the result into one query but not in one column only but instead the result of the second query should be beside the first query.. The result of the second query should be added as a new column.
First Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailReceived
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName;
Second Query
SELECT tbl_uSers.UserName, Count(tbl_rEceived_eMail.EntryID) AS eMailProcessed
FROM tbl_rEceived_eMail INNER JOIN tbl_uSers ON tbl_rEceived_eMail.UseriD = tbl_uSers.UseriD
GROUP BY tbl_uSers.UserName, tbl_rEceived_eMail.ProcessedYN
HAVING (((tbl_rEceived_eMail.ProcessedYN)="Y"));
I need to change rows data into column.
I have a table name as Temp_Report1 with fields Lab_No, Test Name, Reslt, Test_master
Lab_no
Test Name
Result
Test_Master
4.414
Hemoglobin
00
HAEMATOLOGY
4.414
Red Cells
01
HAEMATOLOGY
4.414
E.S.R
02
HAEMATOLOGY
4.414
Reticulocytes
03
HAEMATOLOGY
etc
The another table where I want to copy data is New_repo2 with fields Lab_no, v1, v2, v3 etc. Need as
Lab_No
V1
V2
V3
V4
V5...
4.414
Hemoglobin
Red Cells
E.S.R
Reticulocytes
Etc
How to change this from rows to one column.
I have parent-child one to many data in one pair of relationships, and now I've been asked to see be able to find out what matches a defined regimen; each is also defined in a parent-child relationship.
Best is to show sample data. I'm going to show them as two tables, but the "Components" are actually in a parent-child relationship, e.g.,
PersonList -= Meds
Regimen -= Meds as well
Note that PersonList and Regimen do not really have any relation; we just want to see if things are being done one of the ways they are "supposed" to be done, without a slow manual check. It's worked as set up for reports, and I really don't want to change everything to a big long list of fields, one field per med for a lot of reasons (not least of which is that is denormalizing)
Quy 1 Result:
PersList T1Component
Andrew Med 1
Andrew Med 2
Brett Med 1
Brett Med 3
Brett Med 4
Charles Med 2
Duane Med 1
Duane Med 4
Quy 2 Result
Regimen T2Component
Goody1 Med 1
Goody1 Med 3
Goody1 Med 4
Goody2 Med 1
Goody2 Med 2
I'd like to be able to do two queries - one that are "OK" one that are not. Don't need to replicate the med list, just the regimen if matching..
"Good" would return
Person Regimen
Andrew Goody2 (he has med 1, 3, and 4)
Brett Goody1 (he has med 1 and 2)
"Bad" would return
Person
Charles
Duane
What they "almost match" does not matter; it tells people which ones we need to check into a bit more.
I have the below query (A). How do I calculate the Day of Therapy as shown in B
A
MRN Last Name DOA Service startDate INR Dose WarfID VisitID
12001 Smith 6/28/2013 Warfarin 6/29/2013 1.20 6 mg 10 138
12001 Smith 6/28/2013 Warfarin 6/30/2013 1.20 6 mg 11 138
12001 Smith 6/28/2013 Warfarin 7/1/2013 1.3 6 mg 12 138
12001 Smith 6/28/2013 Warfarin 7/2/2013 1.40 6 mg 13 138
12001 Smith 6/28/2013 Warfarin 7/3/2013 1.40 7.5 mg 14 138
12001 Smith 6/28/2013 Warfarin 7/4/2013 1.70 7.5 mg 15 138
12001 Smith 6/28/2013 Warfarin 7/5/2013 2.00 7.5 mg 16 138
11002 Brown 7/1/2013 Warfarin 7/1/2013 1.00 2 mg 17 139
11002 Brown 7/1/2013 Warfarin 7/2/2013 1.3 4 mg 18 139
11002 Brown 7/1/2013 Warfarin 7/3/2013 1.50 2 mg 19 139
11002 Brown 7/1/2013 Warfarin 7/4/2013 1.8 2 mg 20 139
11002 Brown 7/1/2013 Warfarin 7/5/2013 2.20 2 mg 21 139
1205972 Jones 6/28/2013 Warfarin 7/6/2013 1.0 6.5 mg 29 138
[code]....
What I really want is to calculate the time (# day) it takes to reach a target goal INR (2-3) I have tried DateDiff between DOA (date of admission) and Start date but this obvious won’t work because some patient won’t start on warfarin until later.
Another possibility is to calculate date difference between two rows in same column but I don’t know how For example Pt 1 = It take 7 days to reach an INR of 2, Patient #2 = it takes 5 days to reach an INR 2.2 and Patient #3 takes 6 days to reach an INR of 2.1. Average = 6 days.
I have a query question . How can I extract a CHANGED value" in a column? Example here under table1. Time and TAG makes a record unique, No ID available ...
Table1
time tag value
10:02 Tag1 10
10:02 Tag2 11
10:03 Tag1 10
10:03 Tag2 11
10:04 Tag1 10
10:04 Tag2 12
10:05 Tag1 10
10:05 Tag2 12
10:06 Tag1 10
10:06 Tag2 12
10:07 Tag1 11
10:07 Tag2 12
10:08 Tag1 11
10:08 Tag2 11
I want to have as output
10:04 Tag2 12
10:07 Tag1 11
10:08 Tag2 11
(so at 10:04 tag 2 changed in value and on 10:07 tag1 changed value and 10:08 changed Tag2)
Preferably with the previous value as well, but this is not the highest prio
10:04 Tag2 12 11
10:07 Tag1 11 10
10:08 Tag2 11 12
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
i have been trying to combine data from 2 different sources, to make unique rows: here it is my situation Data in DGV already in dgv, Unbound:
column1 | Qty
"Sugar", 100
"Salt", 100
"Color", 200
"Malto", 150
Data in datatable:
column1 | Qty
"Sugar", 80
"Salt", 60
"Apio", 25
"Lemon", 60
"Color", 60
So i want a control that matches the column1 in datatable against DGV.Column1, and if value matches only add second column value and if not, then make a new row in DGV.
Final decided outcome:
column1 | Qty|Qty2
"Sugar"| 100 | 80
"Salt" | 100 | 60
"Color"| 200 | 60
"Malto"| 150
"Apio" | | 25
Hi friend
I need your help.
I have made 2 query (named "QryReceivesum" & "QryWithdrawsum").
But query have the same common column called "ProductId".
I want to combine 2 query to be just one.
Currently I have mae one query named "QryStock".
but the data from this qry is not correct.
As you can see, it doesn't show the number of product from productID3.
can anyone help me??
So i have a access database with a main field that we can call vendor # (LIFNR) and another called Company code (BUKRS). There are multiple company codes under a single vendor #. Example:
LIFNR
BUKRS
0000010535
1010
0000010535
5060
0000010535
5610
0000010536
1010
0000010536
5060
0000010536
5610
What I am trying to do is create a 3rd column where i can have a unique row for each of these fields without it repeating. There is no unique identifier in this table and that is what i am trying to achieve.
i have a table that has meter readings for copiers on it and dates.
i need to add another column to show the differences in the meter field between itself and the row entered before it (the month before) to find out how many copies were made. i can do this in excel very easily, but cant figure out how i would do it in access
sample:
Date Meter Diff.
06/01/2006 0 0
07/01/2006 1000 1000
08/01/2006 1500 500
we will be entering the date and the meters in every month, but i need to have it calculate the difference between months so i can then calculate overage, if any.
I am trying to devise a way to display query results in a row instead of a column. The table on which the query is built has this structure:
Column1: Index (pkf)
Column2: Group (int)
Column3: Position (int)
Column3: ItemCode (txt)
When I do a select query, the data comes back like this:
Group Position ItemCode
11A
12B
13C
14D
15E
16F
Etc.
I want to arrange the data this way:
1ABCDE (Etc)
I keep thinking that a cross-tab query is the answer, but all I seem to be able to do is to get counts of Position 1, 2, 3 Etc.
I’m baffled. Does anyone have a suggestion?
Thanks,
Rick
How do I sum up the rows of a column in a report ? My example is here below
Sick Days
Employee1
Employee2
Employee3
Total of Sick Days
I have an Excel sheet with data organized like:
1 A
1 B
2 A
2 B
2 C
I want to reduce the first column to unique records, and have the second column listed on the same row. (I'm assuming I'll need Access to do it.) Is there a way to produce a table like this?
1 A B
2 A B C
The final result would need to be something I could put back into Excel.
I am still quite new to Access and have spend the last two days looking for a solution to an issue but don't seem to find anything that fits. The problem is that I want the three year average of [NIbyA] inserted in [AvgNIbyA]. The years are stored in [FocalYear] (as Date/Time formate) and [TextYear] (as text formate) and [ID] indicates which entity the record belongs to (see below).
UniqueID
ID
TextYear
FocalYear
NIbyA
AvgNIbyA
1020180-19981231
1020180
19981231
[Code]...
Example.accdb
I have been playing around with update queries but can not seem to get the syntax right. The query needs to add NIbyA for year 1998, 1999 and 2000, divide it by 3 (or use Avg() function or something of that sort) and insert the result into the row where [FocalYear] = 13/12/2000. To add to the confusion, it should obviously only add those records that belong to the same ID. Years are from 1998 to 2010 and ID's are random (this obviously means that the rows where [FocalYear] = 13/12/1998 and [FocalYear] = 13/12/1999 will not have an entry).
I'm trying to update all the rows in a column (column A, PO Number) within a table (iSupplierTable). The value (txtPONbr) is entered by the user on a form (NewPO).
Code:
Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_Click_Error
Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "iSupplierTable"
[code]....
I have a query based on 2 tables, joined on Memberid, the result showing :
Table1 Table1 Table1 Table2 Table 2 Table2
Category Association Memberid CustomerName E-mailAddress MemberID
Board Member(Lookup,integer) SAMGA(Lookup,integer) 44 Smith smith@abc.co.za 44
Board Member ADHTY 44 Smith smith@abc.co.za 44
Grower SAMGA 44 Smith smith@abc.co.za 44
I only want to show 1 row, based on the duplication of E-mail address. I know i should be using the row_number function, but cannot get to the result I want.
I have an Access Database and I want to split up one column into multiple columns. The one column has multiple rows that relate to one person, so for instance I have the following:I am taking information from both tables and I want to create a query but I need to separate out the mother, father, and guardian with the names attached
So from the Child table :
Joe Smith Address Phone
The query that I created is from two tables, the child table and the family table: ( I used the dashes just for it to be easier to read)
------Child Table/PrimaryKey --------Family Table---------------------
First Name--- Last Name--- First Name--- Last Name--- Relationship
Joe ----------------Smith ----------Jane --------Doe-------------Mother
Joe ----------------Smith ----------John---------Smith---------- Father
So in this scenario I need to get the name of the second parent in another column....meaning have all the parents appear on the same line for this child, and each child there after..
So I need it to look like this:
------Child Table/PrimaryKey --------Family Table---------------------
First Name------ Last Name------ First Name------ Last Name------ Relationship------ 2First Name------2Last Name------2Relationship
Joe----------------Smith------------ Jane-------------- Doe------------- Mother-----------John---------------- Smith------------ Father
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.
Am trying many times in UNION query but its not working because there is different field names and only some fields are matched. So I need to both query's are combine in 1 query.
They Quays details:
1.Customer Credit Transaction1
2.Customer Credit Transaction2
Above both queries are already combined in UNION query as (Customer Credit Transaction Final) its not a problem.So now I need to do combine the above Union query Customer Credit Transaction Final & ReceiptformQry.
The both query details:
Customer Credit Transaction Final
SELECT BillBook1.TID, BillBook1.BILLNo, BillBook1.BILLDate, BillBook1.Customer, BillBook1.BillMode, [BillBook1 Vat Details].[TOTAL Rs] AS [CC Amount]
FROM BillBook1 INNER JOIN [BillBook1 Vat Details] ON BillBook1.TID = [BillBook1 Vat Details].TID
WHERE (((BillBook1.BillMode)="Credit"));
UNION ALL
[code]...
Above fields are need to merge in Union query or otherwise. and remaining fields are needed to show separately.
Hi,
I have a problem to solve,
Table1:InitialMeasurement
NoQuantity
112
234
345
Table2:FinalMeasurement
NoQuantity
145
377
4 67
Table3:CombinedMeasurement
IndexInitialFinal
11245
2340
34577
4 0 67
how can I join table1 and table2 to table3?
Many thanks,
Galantis
I have the following two queries
Query1:
SELECT TAGS.ID, ALARMS.*
FROM TAGS RIGHT JOIN ALARMS ON TAGS.NAME1=ALARMS.NAME1;
Query2:
SELECT SUBGROUPS.*, Query1.*
FROM SUBGROUPS LEFT JOIN Query1 ON SUBGROUPS.ID=Query1.ID;
How do I combine these to make one?
I have the following 2 queries:
SELECT DISTINCTROW tbl_members.surname, Count(tbl_years.year) AS CountOfyear
FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member
GROUP BY tbl_subscriptions.subscription_fee, tbl_members.surname
HAVING (((tbl_subscriptions.subscription_fee)=0));
This query displays a list with the surname of the member and the Count of the Years he/she did not pay the annual subscription, hence where subscription_fee = 0
TRANSFORM Sum(tbl_subscriptions.subscription_fee) AS SumOfsubscription_fee
SELECT tbl_members.surname, tbl_members.name, tbl_members.mobilephone
FROM tbl_members INNER JOIN (tbl_years INNER JOIN tbl_subscriptions ON (tbl_years.ID_year = tbl_subscriptions.ID_year) AND (tbl_years.ID_year = tbl_subscriptions.ID_year)) ON tbl_members.ID_member = tbl_subscriptions.ID_member
WHERE (((tbl_years.year)>Year(Date())-"6"))
GROUP BY tbl_members.surname, tbl_members.name, tbl_members.mobilephone
PIVOT tbl_years.year;
This query displays a list with the surname, name, mobile phone of the member along with the money he/she paid for the last 5 years as you can see from
WHERE (((tbl_years.year)>Year(Date())-"6"))
My question is: is it possible to combine those 2 lists and have one where all of the following columns will be listed?
Surname, Name, Mobilephone, Count of years with 0 payment, a column for each year of the last 5
Thanks in advance.
I have two queries that I am interested in combining into one if possible. I'm trying to learn Access and SQL on-the-fly, so feel free to point out any noob mistakes I am making.
The first query simply pulls certain records from a table:
SELECT Sensor5.LaneName, Sensor5.SensorTime, Sensor5.Speed, Sensor5.Volume
FROM Sensor5
WHERE (((Sensor5.LaneName)="NB1" Or (Sensor5.LaneName)="NB2" Or (Sensor5.LaneName)="NB3") AND ((Sensor5.Volume)>0) AND ((Sensor5.SensorDate)="4/17/2007" Or (Sensor5.SensorDate)="4/18/2007" Or (Sensor5.SensorDate)="4/19/2007" Or (Sensor5.SensorDate)="4/20/2007" Or (Sensor5.SensorDate)="4/23/2007" Or (Sensor5.SensorDate)="4/24/2007" Or (Sensor5.SensorDate)="4/25/2007" Or (Sensor5.SensorDate)="4/26/2007" Or (Sensor5.SensorDate)="4/27/2007"));
The second query then takes averages and sums from this first query, grouping by a third field (SensorTime). This results in weeks of data being compiled into a single record for each time interval in a 24-hour period. See below:
SELECT SpeedWeekday5NB.SensorTime, Avg(SpeedWeekday5NB.Speed) AS AvgSpeed, Sum(SpeedWeekday5NB.Volume) AS SumVolume
FROM SpeedWeekday5NB
GROUP BY SpeedWeekday5NB.SensorTime;
Is there any way I can streamline this process by combining the two queries into a more complex single query, or should I leave things as-is? Any advice is much appreciated!
I would like to take these two queries and combine them into one if possible. This is the first query:
SELECT DISTINCT [LINE 2].[CASE ID] INTO [TABLE 1]
FROM [LINE 2] INNER JOIN NOLDBA_OBLIGATION ON [LINE 2].[CASE ID]=NOLDBA_OBLIGATION.ID_CASE
WHERE (((NOLDBA_OBLIGATION.AMT_PERIODIC)>0) AND ((NOLDBA_OBLIGATION.DT_END_OBLIGATION)>#6/30/2007#) AND ((NOLDBA_OBLIGATION.DT_END_VALIDITY)=#12/31/9999#));
And this is the second query which is based on the results of the first query:
SELECT NOLDBA_CASE_ROLLUP.ID_CASE INTO [TABLE 2]
FROM [LINE 2] INNER JOIN (NOLDBA_CASE_ROLLUP LEFT JOIN [TABLE 1] ON NOLDBA_CASE_ROLLUP.ID_CASE = [TABLE 1].[CASE ID]) ON [LINE 2].[CASE ID] = NOLDBA_CASE_ROLLUP.ID_CASE
WHERE ((([TABLE 1].[CASE ID]) Is Null) AND (([NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_OWED]-[NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_PAID])>0))
GROUP BY NOLDBA_CASE_ROLLUP.ID_CASE;
Can this be done and if yes can someone show me how? Thanks
me again...
is it possible to combine two qeuries and run at the same time?
query1
ALTER TABLE customerDETAILS
ADD COLUMN ACCOUNT TEXT(16)
query2
UPDATE customerDETAILS
SET customerDETAILS.ACCOUNT = customerACCOUNT.ACCOUNT_NR
where............
how do i create a column in the Details table and then update it to the ACCOUNT_nr in the Accounts table, at the same time?
regards
B