Queries :: Return Multiple Rows From A Single Record In Database

Jul 16, 2014

For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows

1st Obs Start
1st Obs End
2nd Obs Start
2nd Obs End
3rd Obs Start
3rd Obs End.

I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:

ID......Obs Start.............Obs End........
1....[1st Obs Start].....[1st Obs End]....
1....[2nd Obs Start]....[2nd Obs End]....
2....[1st Obs Start].....[1st Obs End]....
2....[2nd Obs Start]....[2nd Obs End]....
2....[3rd Obs Start].....[3rd Obs End]....
3....[1st Obs Start].....[1st Obs End]....
4....[1st Obs Start].....[1st Obs End]....

etc.

I could then quickly count how many obs periods started within the desired month.

View Replies


ADVERTISEMENT

Queries :: Return Latest Record For Multiple Criteria

Mar 12, 2014

I have a table that contains readings from several pieces of equipment as well as the status of each one. Each record has a timestamp, equipment number, status, etc. What I want is to create a query that will return the latest record for each equipment number. Simplified example table:

Timestamp EquipmentNumber Status
Today ------Machine1 ----------Running
Today ------Machine2 ----------Running
Yesterday -Machine1 ----------Down
Yesterday -Machine2 ----------Running

There are more than 20 different Equipment numbers and they are read several times per day and sometimes some of them get missed. What I'm looking for is a way to get a list of all the machines with their latest reading so they can tell which machines are running and which are down based on the last time they were read (instead of specifying a date). I can get this for one machine with no problem. I'm having trouble getting it for more than one machine. I tried a union query (with just 2 of the machines included for testing) but it only returns the results from one machine:

Code:

SELECT TOP 1 TestCompressorRoundQuery.LoggedAt, TestCompressorRoundQuery.AssetNumber,
TestCompressorRoundQuery.CompressorID, TestCompressorRoundQuery.Status, TestCompressorRoundQuery.CompressorIntegrity, TestCompressorRoundQuery.Notes
FROM TestCompressorRoundQuery
WHERE (((TestCompressorRoundQuery.AssetNumber)="104399"))
UNION ALL

[Code] ....

I'd rather not have to create a seperate query for each machine and then join all of those together! I think perhaps a Union query might not be the correct approach. All the data is coming from only one table.

View 6 Replies View Related

Modules & VBA :: Return Multiple Records In A Single Field

Apr 6, 2014

I'm trying to create a report that's based on a query, and the query has three fields: [PersonName], [PersonDate], [PersonShift]. This table holds records for people that worked on certain days and certain shifts. What I want to do is create a report that gives a graphical calendar display of each day in a month, and on any day that the person has a record (and sometimes there are more than one), I'd like to see just the PersonShift records showing in that day's box.

tblPersons
PersonName PersonDate PersonShift
Jason 4/10/14 FIRST
Jason 4/13/14 FIRST
Jason 4/13/14 SECOND

So if I were to print this report for Jason, I'd get all the days in April laid out like a calendar, and on 4/10/14 you'd see "FIRST" in the box, and on 4/13/14. you'd see FIRST and SECOND in the box. All the other boxes would be blank.how to display the calendar, how to display the dates. I'm able to return records to those boxes by creating 31 separate queries, one for each day of the month, and each query returns records for that day. The queries are added to the report as subreports. It all works beautifully.

The thing is, I'm running 31 queries every time I pull the report. Is there a way to code a single field on a report that will run a SELECT statement on the table using variables that are located in fields elsewhere in the record?

If I have a PersonName field on the header of the report, and I have a PersonDate field in the detail of the report's record, can I create a new field in the detail of that record that runs a SELECT statement on qryPersons, and filters the tblPersons by the PersonName on the header of the report and on PersonDate in the record?

I want a field on a report that runs a SELECT statement on tblPersons, I want the field to return only the PersonShift records for that person based upon the PersonDate. Each of the fields on my report have a CalendarDate field, and I want the SELECT statement to return records where the PersonDate matches the CalendarDate, and again, it should only display PersonShift records.

View 2 Replies View Related

Export Multiple Rows Into A Single Cell In Excel?

Sep 18, 2012

I need to export a list of data into a single cell in Excel.

As an example the list looks like this in Access:

450a

650b

320c

4100d

and exports into individual cells. I need it to export into one cell and look like this:

450a, 650b, 320c, 4100d

View 4 Replies View Related

Modules & VBA :: Concatenate Data From Multiple Rows Into Single Row / Field

Nov 4, 2013

I use following function to concatenate data from multiple rows into a single row/field. It puts a comma ',' after each concatenate. What I would like it to do more is to put 'and' instead of comma between the last two concatenated words. For example: Now it gives me Value 1 but I want to get Value 2.

Value 1:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs, community residents

Value 2:
FormRegNo CombinedParticipants
DC-190 Political parties, CSOs and community residents

Function:
Option Compare Database
Option Explicit
Public Function ConcatField(strSQL As String) As String
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strConcat As String

[Code] ....

View 2 Replies View Related

Queries :: Return Entire Rows With Min Date Only

Apr 15, 2015

I am looking to return one row from groups of the same EpisodeID whereby the row with the minimum date is selected each time. This includes returning all other fields in the row such as EventID below and ideally others as well if that will be possible.

To illustrate I include the following. What Access 2003 query would I need to return all the rows with the earliest dates? EventID will be unique in the intial table.

Code:
EventsTable
```````````
EpisodeID | EventID | EventDate
-------------+---------------+-------------
1 | 001 | 01/02/2010
1 | 023 | 05/10/2009
1 | 103 | 12/02/2010
2 | 004 | 02/03/2013
2 | 102 | 12/10/2014
3 | 546 | 04/05/2012
3 | 100 | 08/08/2013
3 | 034 | 10/10/2012
3 | 066 | 02/03/2013
4 | 777 | 05/07/2014
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011

Code:
ExpectedOutput
``````````````
EpisodeID | EventID | EventDate
-------------+-------------------+---------------
1 | 023 | 05/10/2009
2 | 004 | 02/03/2013
3 | 546 | 04/05/2012
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011

View 13 Replies View Related

Queries :: Return A List Of Rows Sorted By Service Type Ascending

May 20, 2014

In a query I'm trying to return a list of rows sorted by Service Type Ascending and then the last item in the list should be a row called "Add Edit Value".If I 'ORDER BY 2' then the "Add/Edit" row appears at the top which is not what i want.

My SQL:
SELECT '' As ServiceTypeID, 'ADD/EDIT VALUES' As ServiceType FROM ServiceTypes UNION SELECT ServiceTypes.ServiceTypeID, ServiceTypes.ServiceType FROM ServiceTypes
ORDER BY 1 DESC;

View 5 Replies View Related

Queries :: Add Multiple From One Table To A Single Record On Another Table

Aug 1, 2013

I have two tables

tbl_Orders
Cust OrderNo OrdLne Prod Qty
ABC SO123 1 AA 15
ABB SO124 1 AB 10

tbl_StockLoc
Prod Loc Qty
AA ZX12 10
AA ZZ12 10
AA ZZ13 50
AB CW12 15

One product can have up to 10 locations in the tbl_StockLoc

I need to print the following report (up to LOC10)

Cust OrderNo OrdLne Prod Qty Loc1 Qty1 Loc2 Qty2 Loc3 Qty3 ....
ABC SO123 1 AA 15 ZX12 10 ZZ12 10 ZZ13 50
ABB SO124 1 AB 10 CW12 15

View 1 Replies View Related

Modules & VBA :: Return Rows In Query From Variant Array Return From UDF

Sep 16, 2014

I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.

So if my input table looks like this

[strField]
"kick the ball"
"return the pass"

my query result should looks like this

[Orig] [new]
"kick the ball" "kick"
"kick the ball" "the"
"kick the ball" "ball"
"return the pass" "return"
"return the pass" "the"
"return the pass" "pass"

Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.

With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.

View 3 Replies View Related

Multiple Links On Single Database Field

Dec 28, 2006

Hi, this question is more about Microsoft Access than it is about ASP. However it involves a database that I am setting up for a website being written in ASP.

I have a table which is going to store business details. Every business belongs to a business category and related subcategory. For example Garages and MOT Centre.

I want to store up to 5 categories for each business. Is it possible to link to the categories and subcategories tables for each of the 5 category fields in the business table?

Thanks, Steve (Blackpool)

View 1 Replies View Related

Multiple Notes - Single Record

Jul 20, 2015

So I have a list of jobs and each job is subject to change from the original plan.

Each change needs to be documented and dated. So what I want is a form where the site can be selected and a note written. When this is saved, a record of the current time would also be needed.

The form aspect of this seems fairly straight forward to me. I just cant visualise where the 'notes' will be stored.

View 2 Replies View Related

Multiple Rows Per Record?

Oct 10, 2007

I have a table of products and a table of ordersEach product requires multiple processes to completeCan I make a query that will lookup the products in the orders table and show a list of all the processes that need to be done to complete all the products on order?The bit I dont get is how can a query return multiple rows for each product?

View 3 Replies View Related

Multiple Lines In Order Form From Single Database

Feb 28, 2005

I have 2 databases Product, with 3 entries ProdID, ProdName and UnitCost.
2nd database Order has Order ID, ProdName, UnitCost, Quantity, SaleDate and Total.
I want to create a form to input data into the Order databse that allows me to enter several rows using the data from the product table by selecting ProdName(Product) from list and UnitCost(Product) being inserted into each row automatically as a new ProdName is selected.
Can anyone hsuggest how best to do this.

View 4 Replies View Related

Tables :: Set Up Attendance Database That Has Multiple Status Available For Single Day

Dec 9, 2012

I need to set up an attendance database, that has multiple statuses available for a single day.Example: employee can be present, he can have a sick leave, he can be away on training, or business trip, etc... And for some of those statuses, like business trip, i need to be able to freely enter a comment, stating where he is etc..

Anyway, the key is that this database should be able to offer a "headcount" option, and traceability for past statuses for at least a year, for every and all employees. Now i just need to set up the database tables and relationships.

View 6 Replies View Related

Tables :: Database With Multiple Entries For A Single Date?

May 17, 2013

We have two facilities that receive four different types of shipments. I wanted to set up a database to track these shipments. Is there a way for me to set up tables such that the person entering the information can leverage the same date and location field for each entry type? For example, we may only receive two types of shipments today and four tomorrow. I want to avoid having the clerk enter the date and location two times today and four times tomorrow for each data entry.

Here's an example of what I mean above:

Date Location Type Qty
5/16 1 A 10
5/16 1 B 1
5/17 1 A 1,000
5/17 1 B 100
5/17 1 C 1
5/17 1 D 11

I'd like for the clerk to select from a fixed number of locations (1 and 2) and a fixed number of types (A, B, C, or D) and provide the date and quantity received.

View 4 Replies View Related

Charting Multiple Values From Single Record

Feb 11, 2008

This particular database generally looks at reporting individual records as opposed to summary reports. So for a particular data record i have the following data [x1][x2][x3][x4][x5][y1][y2][y3][y4][y5]

I need to chart (scatter) these figures as paired x,y variables for display on a feedback report such as

x1,y1
x2,y2
x3,y3
x4,y4
x5,y5

For some reason i simply can't wrap my head around haw to make this happen.

Many Thanks in advance for any help.

Cheers
ndeans

View 2 Replies View Related

Saving Multiple Values To A Single Record

Oct 22, 2005

I want to have a box with yes/no choices defining the Lessons a Student chooses. Sth like this
Because the Lessons are quite plenty and they change from time to time, I don't want to have them predefined in the Sudent table, but to have a related table containg the Lessons providing more flexibility.
My problem is how to assign the Yes/No values from the form to a single record. ANy suggestions?
A first thought is to create a List from Lessons table, with 2 columns. 1st the Lesson and 2nd the Y/N box. Every Lesson would have a value like this:
1,2,4,8,16,32,64...
With this way the sum of the Lessons would be unique for each combination and it could be saved in a single record. But such a solution is not so practical.
Any alternatives?

Thnx in advance

View 1 Replies View Related

Assign Multiple Names To Single Record

Jul 18, 2013

I have a Project Database where I keep track of projects throughout my plant. A project can have one owner or several owners working on it. Is there a way to assign multiple owners to a project?

I tried doing a subform but I don't like how it looks on the form and when I try to do a query that filters based on a person's name, the form has to be a single form rather than a continuous form which is not what I want.

Is there another way that I can accomplish this?

View 1 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

Modules & VBA :: Update Access Database (multiple Rows)

Jan 7, 2014

I have a table called "Mov" and its columns are:

Code:
Number | Link | Name | Status
1899 | htto://example.com/code1 | code1 | Done
2 | htto://example.com/code23455 | code23455 | Done
3 | htto://example.com/code2343 | code2343 | Done
13500 | htto://example.com/code234cv | code234cv | Deleted
220 | htto://example.com/code234cv | code234cv | Null
400 | htto://example.com/code234cv | code234cv | Null

So I want a way to update Status of my rows according to numbers list. For example I want to update Status column for multiple numbers to become Done .

Simply I want to update "Null status" to become "Done" according to its numbers according to this list

Code:
1234
53
546
767
2135
6657
43
34

Something like this

I tried "update query" but I don't know how to use criteria to solve this problem. In Excel I did that by "conditional formatting duplicates" -with my number list which I wanted to update - Then "sort by highlighted color" then "fill copy" the status with the value...

View 4 Replies View Related

Tables :: Field For Multiple Sales On Single Record

May 7, 2013

I have a tblCustomer table to track customer information. One field is for flights the customer books. I expect that a customer might book more than one flight, which should affect what he owes. (My tblFlights table includes fields for the cost of each flight.) How should I make the Flight field in tblCustomer so it accepts multiple flights and reflects on the customer's final bill and my own accounting tables that I haven't even created, yet?

I'm probably overthinking everything. This is just an Intro to Access class, and I only need 3 tables with 1 relationship. So far, I have 9 primary tables, 4 duplicate tables (for M:N relationships) and 11 relationships. I have to have tables for financial data, customer info and product/service info. I wasn't sure what I would need for my fictional airline, so I created tables to track everything I could think of, hoping to cover all 3 required types of data in the process.

View 2 Replies View Related

Consolidating Multiple Queries Into One Single Result

Dec 17, 2006

I couldn't find a similar question already answered, so here it goes. I have multiple tables used to store information on different research materials:

table Books:
- BookID
- Author
- Title
- Year
- Cabinet

table Papers:
- PaperId
- Author
- Title
- Year
- Cabinet

table Catalog:
- CatalogID
- Author
- Title
- Year
- Cabinet

And I have a query for each of those tables that helps me find information. What I'm trying to do is to consolidate the result of those individual queries into one single query that later I can insert into a form to display a search result.

So the resulting query would look like:

Type.....ID....Author.....Title....Year....Cabinet
Book.....001...John.....Good book....2005....C01
Book....002.....Mary....Other book...2006...C01
Paper....001....Albert...PaperABC....1987....C01
Paper...002....John.....PaperXYZ.....2006....C02
Catalog...001...Mark....Cat00A....1989.....C02
Catalog...002...Bill......Cat00B.....2004.....C03
etc.

Is this possible?

Any help is appreciated!

Thanks.
Daniel

View 4 Replies View Related

Single Set Of Parameters For Multiple Queries In A Report?

Oct 21, 2004

I'm pretty new to Access, so if I'm doing this the hard way, that's why.

I work in a quality control position, and I'm trying to set up a single Access database for the QC staff to use instead of everyone having their own seperate Excel workbooks.

The issue I'm running into at the moment is on a report. Each record is graded on four seperate types of criteria, Error Type 1, Error Type 2, Error Type 3 and Error Type 4, all of which need to be reported on seperately. So I have at least four queries set up, all with the same parameters (right now, just review date.) I'm trying to pull through all four queries on the same report, and so far I've had success having the report ask for the parameters only once and then applying it to all four queries. However, I'm running into a problem where Access is now forcing the filters of each individual query on to each of the other three queries, so it's only pulling through records for all four queries that match the criteria of all four. Any record which only matches the criteria of one, two or three of the queries is being left off the report.

Sorry if this sounds confusing. Anyone have an idea as to what I'm doing wrong?

View 3 Replies View Related

Update Queries On Multiple Rows

Mar 16, 2007

Hi,

Is it possible to update multiple rows in a Table at one time using Update Query?

Ta
Kasey

View 5 Replies View Related

Queries :: Concatenate Multiple Rows Without VBA

Sep 23, 2014

I am trying to concatenate multiple rows into one record. I googled it and found many functions in VBA that do the job. However my problem is that my query will be linked directly to Excel file and then I get an error message saying that Excel could not recognise this function. I could potentially insert data into new table and link that table to Excel but I need to avoid end-user exposure to Access as much as possible.

So I am desperately trying to find a way of doing this without VBA code.

Example of data:

Customer Name |Order Number
Smith |O101
Brown |O102
Smith |O103
Green |O104
Brown |O105

I am trying to achieve below:

Customer Name |Order Number
Smith |O101, O103
Brown |O102, O105
Green |O104

View 14 Replies View Related

Queries :: Multiple Rows In A Query?

Jun 10, 2013

I have a table where and account could have multiple rows with different data like Applied_Date and Trans_Code, and AMOUNT. The AMOUNT in two of the rows will be a positive and a negative and will be zero each other out. I am trying to create a query that will only return the the rows that do not offset each other. Here is an example of my table:

ACCOUNT CODE APPLIED_DATE AMOUNT
292020 M 5/11/2012 ($33.95)
292020 11 5/14/2012 $33.95
292020 A 5/30/2012 ($33.95)

View 3 Replies View Related







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