Group By And String

Feb 25, 2008

Perhaps this is not the right description but I needed a tittle.

I have a query that consists out of multiple table.

The query contains 3 fields:
1. Course_ID
2. Part_ID
3. Page

I grouped the first 2 but here is the thing:
I want the contents of the page field to be displayed next to each other like this.

Query without grouping:

Course_ID Part_ID Page
1 | 2 | A
1 | 2 | B
1 | 2 | C
2 | 4 | S
2 | 2 | V

And it should be this:
Course_ID Part_ID Page
1 | 2 | A, B, C
2 | 4 | S
2 | 2 | V

How can I achieve this?

View Replies


ADVERTISEMENT

General :: String Manipulation On A Table - Text Is Gibberish In Group By Query

Jun 22, 2015

While processing string manipulation on a table (140K records, 200-250 MB) the file has corrupted, and I lost all vba Modules, but the data and other DB objects seemed to be in tact.

I now have a query with a text field, when I make a simple join SELECT, the text comes in fine (and, of course, seems fine when presented in table), but when Group by - I get Gibarish: "CARVEDILOL 6.25MG, TABLETS"

Is presented in Group by as: "ç …5"

I have recreated the file, importing queries, including this one, but then recreated it as a totally new query, but get that same results.

File Corruption? anything to do about it?

View 12 Replies View Related

Reports :: Sum Group Items By Specific Item And Hide Details Of Group Summing

May 29, 2015

Despite Google I can't seem to figure this out.

I have some data in a format similar to:

Name / Style / description / speed / distance
john / driver / careful / 80 / 5500
mary / driver / careful / 70 / 7000
pat / racer / reckless / 100 / 6000
anne / driver / careful / 75 / 1000
peter / racer / reckless / 110 / 6500
don / snail / slow / 60 / 6000

I want my report to total by style, without details and to look like:

driver careful 13500
racer reckless 12500
snail slow 6000

How do you get a report to sum the group items by a specific item and to hide the details of that group summing?

View 2 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

Reports :: Control Group Expression For Group In Report?

Mar 28, 2013

Is there a way to have an expression in the control source of a text box in a report, that re-starts or is exclusive for every group within the report?

View 5 Replies View Related

String Validation (string Must Start With Http://)

Mar 12, 2007

Hi all,

I was looking for some help. I am trying to setup a table with a field for web address. People are entering www.website.com etc however I need them to make sure it starts with http:// Is their any way I can put validation on the field to make sure that this is entered? Or maybe I could use an input mask?

Any suggestions would be gratefully recieved.

Andy.

View 3 Replies View Related

Forms :: Option Group - Warn User When Neither Of 2 In Option Group Not Ticked?

Jul 25, 2013

I stumbled upon the Option Group function just yesterday and, happy as a clam, I created a group with 2 options in radio button style. I assigned the values to a field called Registration_Type as the 2 options are "Confirmed Registrants" and "Prospective Attendees".

[Great. That part works well. When I look at the table, a 1 or a 2 is in that field so it's great to know how to control accidental ticking of radio buttons (previous 450 records or so didn't have this option group functionality so one might easily tick one of the buttons. So one part of controlling option group I know I can handle via the table itself for now.]

The challenge is how to ensure the user always ticks one or the other ... I went back to the main table and tested the 'required entry' option for the Registration_Type field but forcing an action like this is not ideal in my mind. The usual error message vagueness for the average user is no good and I don't want to limit the user so much.

Is there a way to simply have a popup come up warning that neither radio button was ticked? Perhaps something linked to the form - i.e., maybe "after update"?? I only learned about attaching code to before and after update on controls a couple of days ago, so not sure if this would be best approach.

Just something to let the user know that nothing has been ticked in the option group as that controls in which of 2 reports the data will show up in so any record not ticked might mean a registrant being left out, which would be rather disastrous <g>.

View 1 Replies View Related

Search For A String Within A String

Oct 27, 2006

I have a column called CPU_S within a table called workstation that contains sample text like P111 933

I want to use the update command to search the CPU_S column for entries that contain this in there string then add P3 to a column called CPU_N

So far I have the code below but I don't know how to search a column entry for a specific string within a string. Can this be done and how?


UPDATE workstations SET CPU_N = "P4"
WHERE CPU_S = ;

View 3 Replies View Related

SUM And GROUP BY

Mar 12, 2007

I'm trying to create a Query. I have a field "Name" from the table "Products" and the field "Quantity" from the table "Deliveries". Currently, The same product name will appear several times with their quantities. I want to SUM all of those quantities together and only have the Product name appear once. If it's a diffrent product name, then sum all of it's quantities together etc. I'm having a tough time writing a proper formula, It either has syntax errors or produces more than one field, which I don't understand. I need a formula that I can insert into the design view of the query. This formula will also create a new data field to hold the information. Can anyone help?

p.s. Do I have to use SELECT for both Name and Quantity? Where do I insert GROUP BY? I'm so confused!! I've tried things like (SELECT Name, (SELECT SUM(Quantity) FROM Deliveries) FROM Products....by now where am I supposed to write GROUP BY???

View 5 Replies View Related

Top N Per Group

Mar 22, 2008

I know you have probably seen this a trillion times, but being new to all this, it is discouraging to say the least! Please help...

I think maybe I should upload the database, let me know.

Here are the names:

Table name: PPM Data

Fields:
Company
PPM Month
PPM Amount

There are 10 different companies, and I need top 6 months data per each Company, to eventually only show the 6 amounts. I have tried all the equations I can find. But I don't think I am doing it right. I have tried to make 10 different queries for each company and than tried to join those queries, and that just didn't work.

I have tried Allen Browne, confusing, and I have tried Acc2002 for their equations for Top N per group, I still can't make out what they are saying without the actual database to look at.

View 6 Replies View Related

Group

Sep 13, 2005

Hi,
i have a combo box that query a tabel to get the date's i chose from but she repeat the samedate if i have a 2 records with date 12-9-2005 i see 2 in the combo box ho i can join them to see only one date fron them

View 1 Replies View Related

String Not Getting Any Value

Jun 8, 2005

It worked in acces 2000, and now am using acces 2003. Anybody any clue why the string strVoorstel would not get the value? It just returns nothing.

Here's the code:

Dim strVoorstel As String
Dim lngTel As Long
Dim strQer As String
Dim strDa As String
Dim lngNum As Long

...........

View 6 Replies View Related

SQL String

Jul 26, 2006

Hi Folks,

i am using the sql below but i am having a little syntax error:

i am trying to refer to a variable called strRAGCol


strSQL = "SELECT tblSite.SiteID, tblSite.SiteRAG, tblSite.Active " _
& " FROM tblSite " _
& " WHERE (((tblSite.Active)=Yes)AND((tblSite.SiteRAG)= strRAGCol));"

Any help much appreciated.

Mark

View 4 Replies View Related

SQL String Help Please

Sep 29, 2005

The following SQL query is returning no records when I know for a fact there are some there! Can anyone please hlp me?!

Dim strSQL As String
strSQL = "SELECT * FROM tblHirer WHERE HirerSurname= 'Forms!frmFinanceProposal!Child845!Text430'"
rsFindDuplicates.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rsFindDuplicates.RecordCount


Also, I was wondering how I would put as 2nd filter on the recordset to equal a particular date?

Cheers

View 2 Replies View Related

Sql String

Dec 16, 2004

I had a query and the sql is:

SELECT Institutions.*, Institutions.merge, Institutions.Inst_type
FROM Institutions
WHERE (((Institutions.merge)=True) AND ((Institutions.Inst_type)=[forms]![merge_inst_type]![Inst_type]));

and I had a command button which process

MergeAllWord ("select * from merge_query_true")

but I am getting an error like this "make sure the sql is correct , sql was
select * from merge_query_true

select * from merge_query_true ---- is this code ok?

If I had cut the part "AND ((Institutions.Inst_type)=[forms]![merge_inst_type]![Inst_type])); from the sql it works fine....

View 4 Replies View Related

SQL String

May 30, 2007

WHat am I doing wrong...I cannot get the syntax correct...

Form the Query Builder:

SELECT tbl_GroupMembers.GroupNumber, tbl_GroupMembers.Email
FROM tbl_GroupMembers
WHERE (((tbl_GroupMembers.GroupNumber)="10"));


NEED IT TO BE IN THIS SQL STATEMENT:

Set rs = dbs.OpenRecordset("Select * From tbl_Groups", dbOpenDynaset)

View 3 Replies View Related

Can't Group On Memo

Jan 18, 2006

I have a field on a table that is a memo field. I am trying to write a query to include this field but get the "Can't group on memo or OLE object. Any ideas on why and how can I query with this memo field included?

View 2 Replies View Related

Show Top 2 By Group

Feb 11, 2006

I'm trying to view the last two valuations for each property so that they can be compared. I can pull out the last valuation for each property and can pull out the top two records but not for each property, just shows me the top 2 of all records.

Any suggestions?

View 5 Replies View Related

Group Permissions

Feb 28, 2005

Hi, I have just made my maiden voyage on the good ship "Security Wizard" and the water has been a little rough but as yet I still have my sea-legs intact!

Slight problem though... I want users to be able to add data to a table but not to be able to view the whole table if they try and open it in the database window. Now I know reasonable measures would just hide the DB window but I have the threat of Data Protection Auditors looming over me becuase management "forgot" to tell me security was required and it has already been breached before I used the wizard.

Anyway, I did some research on here and I found a method whereby I can set the table so only I have permission to view/add data, then I can get users to add data via a query with permissions set to Owners.

I'm really hoping that someone will come to my rescue and tell me that there's a much easier way than that...

Please....

Anyone....

Thanks, Tom.

View 1 Replies View Related

GROUP BY Just One Criterion

Jun 11, 2005

I need to execute this query from an ASP . Net page. I'm connected to an Access 2002 DB.

SELECT tblBudget.GroupCode, tblBudget.GCDescriptioon, tblBudget.Amount
FROM tblBudget
GROUP BY tblBudget.GroupCode, tblBudget.GCDescription, tblBudget.Amount
ORDER BY tblBudget.GroupCode;

If I delete tblBudget.GCDescription and tblBudget.Amount from the "GROUP BY" instruction it does not work.

What I want to do is that in case tblBudget.GroupCode is repeated (e.g. several records with the same group code) I want to display only one line summarizing all info for the given group code.

Thank you

View 1 Replies View Related

Combining GROUP BY And SUM

Jul 3, 2005

http://www.warwickwebvisions.co.uk/MA/query.gif

The image above shows results from a query I've created. However, I need to group the results by Product Id and display a sum of the individual Product Id's. So I should have something like:


Commercial Loan Mr Steve F Cobblestone LNCLCLRE £299,491.33
Demand Deposit Mr Steve F Cobblestone DD033 £7,402.81
Demand Deposit Mr Steve F Cobblestone DD043 £3,929.78
Time Deposit Mr Steve F Cobblestone TD013 £12,195.60
Time Deposit Mr Steve F Cobblestone TD018 £961,924.12
Time Deposit Mr Steve F Cobblestone TD025 £113,483.58



Any ideas how I construct a query to display the results as per above??

View 1 Replies View Related

Group By More Than 1 Hour

Jul 25, 2005

i have a table of time and dates and a value after it inn which i want to do a query which groups these values by every 3 hours e.g

my table
12/02/2001 00:00 6
12/02/2001 01:00 4
12/02/2001 02:00 8
12/02/2001 03:00 12
12/02/2001 04:00 7
12/02/2001 05:00 6


i want the query to do this
12/02/2001 02:00 18
12/02/2001 05:00 25

i cant find anything anywahere and its use ids for a chart in which the chart wizard allows grouping by 1 hour and not by more than 1 hour any help will be much obliged

cheers

View 1 Replies View Related

How To Group Records

Dec 4, 2005

Hi,

I've got a database containing three tables:


=====================
tblEvent:
---------------------------
Id (PK) | Event_Title | Event_Price
=====================


=====================
tblDelegate:
---------------------------
Id (PK) | Delegate_Name
=====================


=====================
tblBookings:
---------------------------
Id (PK) | Event_Id (FK) | Delegate_Id (FK)
=====================



I need to output an HTML table with the following headings:

==============================================
Event Title | Event Price | No. Delegates Booked | Total Price
==============================================

How do I group(/join?) the tables to show this data? I'm confident with doing the recordset stuff, it's just that I have no idea how to build a SELECT string, with data grouped into events where the total number of delegates who've booked on that event will be added together.

Does this make sense?

Any help would be much appreciated, thanks!

View 6 Replies View Related

Group By Query

Mar 26, 2006

Hello,

I am using Access 97 and I am getting very stuck when I am making a group by query.

Basically I have a table which has for engineering jobs (and a few other collums):
[id], [Site Code], [Ref Number], [Job Size], [Date Origionated] and [Date Completed]

I then have made a query (called [MG - CWD SLA]) which works out for all jobs between 2 dates, how long they have been running for and if they have met the Service Level Agreeement:

SELECT Sheet1.[SITE CODE], Sheet1.[Ref Number], Sheet1.[Job Size], Sheet1.[DATE ORIGINATED], Sheet1.[JOB COMPLETE], (CalcWorkdays([DATE ORIGINATED],[JOB COMPLETE])) AS Expr1, IIf(IsNull(Sheet1.[JOB COMPLETE]),"Not finished",IIf([Job Size] Like "M*",IIf(Expr1<=10,"Y","N"),IIf([Job Size] Like "S*",IIf(Expr1<=5,"Y","N"),iif([Ref Number] LIKE "ID*",iif(Expr1 <= 1,"Y","N"),"N/A")))) AS Expr2
FROM Sheet1
WHERE (((Sheet1.[Help Desk Type])="CWD") AND ((Sheet1.[DATE ORIGINATED]) Between [Type the beginning date:] And [Type the ending date:]))
ORDER BY [DATE ORIGINATED];

I then have made a summary query based on the above query which gives me for every site, between 2 dates; the number of jobs who have met the SLA or not as the case may be.

SELECT [MG - CWD SLA].[SITE CODE], [MG - CWD SLA].Expr2, count(*)
FROM [MG - CWD SLA]
GROUP BY [MG - CWD SLA].[SITE CODE], [MG - CWD SLA].Expr2;

What I would like to do is for every like outputted by this query, give the number of jobs for this site between those 2 dates, so then for every line of the query give the percent of jobs which met the sla/not met the sla or not finished.

I have been messing around with distinct and I cannot get this to work! Also been trying to do a join or a subquery, but not sure how to integrate this with working with a query based on a query.

Any help would be great!

Thanks,

Steve

View 2 Replies View Related

Crosstab Group By On Value

Mar 29, 2006

Hi there I have the following predicament,

I want to run a crosstab query on a table that looks like the below:

ID STORE RECEIVED ITEM RESPONSE
1, 123, 2006-03-30, Name, Tony
1, 123, 2006-03-30, Position, Baron
1, 123, 2006-03-30, Name, Simon
1, 123, 2006-03-30, Position, Mgr
1, 321, 2006-03-30, Name, Sarah
1, 321, 2006-03-30, Position, Analyst

The crosstab query as it stands only pulls back the first and last records as it wont allow grouping on the value field, which in this case is the response field. All other fields are row headings.

The report looks like this:
1, 123, 2006-03-30, Tony, Baron
1, 321, 2006-03-30, Sarah, Analyst

Any ideas on how I can bring back both reccords, eg Group on the all fields? It should look like this:

1, 123, 2006-03-30, Tony, Baron
1, 123, 2006-03-30, Simon, Baron
1, 321, 2006-03-30, Sarah, Analyst

Thanks for your time!

Cheers

Tony

View 1 Replies View Related

Having Or Group Clause

Jun 2, 2006

i have to list BookID numbers borrowed by patrons where fine are greater than a set amount

View 1 Replies View Related







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