Group By Customer And List A Field

Apr 28, 2008

Hi all,

I have a data set such as:

CustomerIDCompanyNameSiteNameStrataIDRUID
4002Severn Trent Water LimitedFRANKLEY3~00074832
4003Severn Trent Water LimitedFRANKLEY3~00074831

I would like to only have 1 record of the Company name/ID with the RUIDs listed almost in one record something like...

CustomerIDCompanyNameSiteNameStrataIDRUID
4002Severn Trent Water LimitedFRANKLEY3~00074832, ~00074831

Can we do this in SQL? (Specifically and Append query)

Thanks for any suggestions.

View Replies


ADVERTISEMENT

Queries :: Create Query To List Hires By Customer Age Group

Jan 24, 2014

I am doing my project to create data base for Video Hire shop. Was allright so far; however hit the wall now.
I need to create query to list Hires(rentals) by customer age group. I have created query with the following fields: Customer ID, date (Date()), DOB field and calculating field: AgeGroup:Now()-[DOB]. When click to display data in AgeGroup field is displayed in days. How to set it up to display decades not number of days days?

View 1 Replies View Related

General :: Create Check On A Field (customer ID) Of Customer Table In MS Access?

Sep 7, 2012

create a check on a feild(customer id) of customer table in MS Access , as "Customer ID is of 8 characters, the first 4 are alphabets and the last 4 are digits"

View 6 Replies View Related

Reports :: Training Database - Group Report By Field List Row Source

Oct 30, 2014

I am in the process of creating a training database that includes levels of proficiency with certain tasks for employees.

In one of my reports I would like to appropriately display with tasks the employee "Cannot Perform";"Can Perform with Assistance";"Can Perform Alone";"Trainer" (straight from the field list of the task). But I can't seem to get the hierarchy correct. Tried it in a PivotTable too as I thought similar to PTs in Excel you could get some kind of "count" of values. Couldn't make that happen either.

Each employee has a proficiency rating on about 20 different tasks. Proficiency input is controlled by a field list. I would like to structure this part of the report like so:

------------------------------------------------------------
EMPLOYEE PROFICIENCIES
--Cannot Perform Task
----Cutting
----Trimming
----Grinding
--Can Perform With Assistance
----Painting
----Fixing
----Drilling

etc. etc. So in this case the Field itself would become the value being grouped. I know there has to be some logic either in a query or SQL.

View 2 Replies View Related

Customer Drop Down List

Mar 21, 2006

Hey guys, I have a question that I am hoping you can point me in the right direction on. Before I ask it, I am new to access. I have been looking at templates and reading and things on how to build a database. I have my tables setup, relationships I think are right and my form created. One of the things I am having trouble with is creating a drop down list to be able to pick a customer name for. I setup the combo box and have a customerID as the control source in the properties. I have the Row Source Type as table/query and the Row Source query setup so it takes the info from the customer table.

query:

field: customer.* name
table: customer customer

I think I am doing this the same way the templates have the properties but for some reason it won't allow me to pick from the list. It keeps saying "control can't be edited. It's bound to unknown field 'CustomerID'".

I attached my database. I hope it is clear enough that you might see what I have done wrong. Thanks in advance.

View 8 Replies View Related

Forms :: List All Records With Same Customer Name

Sep 11, 2013

Is there a way (via the form) to search for all the records that have the same customer name and have that list show up so the user can select the exact record s/he needs?

View 14 Replies View Related

Updating Email Address In Customer List

Jul 5, 2006

I need to keep a customer list and I need to update the email address of the existing customers in the customer list.

The customer list table called 'masterlist' and the table looks like this:

IDPhone CallerNameContactEmail
112345678Emily Office<blank>
287654321Emily Home<blank>
399536546Emily Mobile<blank>
4 75293924 Jack <j.k@mail.com>

The table contains email address of some of the existing customers, I have the same fields in this table, the table called 'Newemail'.

IDPhone CallerNameContact Email
112345678Emily Officec1@mail.com
287654321Emily Home<blank>
399536546Emily Mobilee1@mail.com
478954648Jenny <blank>
545454854Derek <blank>
645484542Kelvin k1@mail.com

How I can insert the email address from 'Newemail' into my existing customer list 'masterlist' i.e. contact email of ID 1 and 3 :confused: ? I would like to ignore ID 4-5 from the 'Newemail' table. And I would like to keep ID 4 from the 'masterlist' table.

Thanks!! :D

View 6 Replies View Related

RecordsetClone Problem On Northwind's Customer Phone List

Feb 16, 2006

When attempting to select a letter in a form copied from the Northwind's Customer Phone List Form, I am now getting an error message that says "The object doesn't contain the Automation object "RecordsetClone'".
I never had that problem before.
Appears to be something in the Option Group that has gone awry, but I cannot access the Visual Basics behind the Macro.
Is there a way to suppress this message? It does not appear to be a problem in bringing up the sought after page.
:confused: :confused: Please help!!

View 1 Replies View Related

General :: Remove Customer From List But Keep All Related Info

Sep 26, 2012

Let's say I have a list of customers. For each customer I have much more info on other lists (order list, personal info list, bank info list, and so on) - all are of course connected properly.

Now let's say a certain customer is no longer my customer, so I want to remove him from the customer-list. But, I want to move him to a different list - past-customers - so all the information that was related to that customer will remain so. In short, I want to remove from the customer-list without affecting the related data.

View 4 Replies View Related

Customer Discount To Product Discount Group

Mar 22, 2007

Hi I'm hoping someone can help me with this problem (the access problem.. not the other one ! :cool: ) I have a table of Products, each item belongs to a product group, I have a table of Customers, amoungst the normal customer fields I have fields named after the item discount groups so that each customer has his own discount for that group of products... when I enter an item into the sales order form I would like the discount for that customer for that item(item group) to go into the discount box.. sounds easy if you say it fast... I hope someone can help me with this !

View 8 Replies View Related

Add Customer Name Into Table Each Time Unique Customer Added To SaleTable

Dec 11, 2013

I have a Table Sales - that lists customer and order information

Most of the customers are new - is there a way to populate Customer Name Table with a new entry each time a new customer is input into the Sales Tale

(Without having to add the custome to Customer Name Table - then going to Sales Order)
OR
Failing that - is there a way to use a list box that points to Customer Name Table (and if it doesn't find the one you want, will allow you toadd a new record) In the SAME form as you use to add to Sales Table.

So:
Date (textbox linking to form.Sales) Customer Name (List Box linking to form.CustomerName + adding the info to form.Sale)

View 6 Replies View Related

Combining Customer And Customer Contact Tables

Dec 24, 2011

I have a DB set up with a debtors table (Customers) and a Customer Contacts table, I was thinking of combining these two tables into one.Most of the Debtors are companies, but a few are actual people. With the current setup, i need to have both a debtor and a customer in two different tables, but with exactly the same data.

View 1 Replies View Related

Queries :: List Latest In Each Group

Mar 23, 2013

I have a table of SOPs/WIs. Each, with time, gets a new version - this I keep in the child table.

tblVersions
------------
VersionID (PK)
SOPID(FK)
Version (integer)
IssueDate (date)

I want to make a list of the currently valid versions, i.e. for each SOPID pick the VersionID with the latest date.

One can do many things with subqueries and Max - functions, but after some experimentation I have stumbled upon this:

Code:
SELECT Last(tblTest.VersionID) AS LastOfVersionID
FROM tblTest
WHERE (((tblTest.IssueDate)<Date()-10))
GROUP BY tblTest.SOPID;

The function Last has a bit tricky reputation and description on the web. It does yields the right result in my testcase though , see the attached db - run query3.

Query3

Code:
LastOfVersionID
100
90
20
40
666
888
55
8

Update:
Well, it isn't. Just changing the VersionID numbers to something other than an ascending sequence renders an incorrect result with the above query. So what is the best way to get the desired result? (Access 2007)

View 4 Replies View Related

Tables :: Autonumber / Customer Reference Field?

Sep 30, 2014

use an account reference from one field in a table on a second table and add a number after it... e.g.

Table 1 Table 2
PEFA PEFA/001
PEFA PEFA/002
PEFA PEFA/003
PEFA PEFA/004

and so on...

View 2 Replies View Related

Cascade Combo Box Previous CD Group List Box

Apr 29, 2005

I need directions on Cascade Combo Box, So when I select:

CD Group

Displays only the CD's in that Group that are related to that Name
So what fields would I have to set up for CD Group and CD NAme with that Table.
Someone emailed me an article on Cascade combo boxes what fields so I need for the coding.......

View 2 Replies View Related

Can Change Group Of Checkboxes To Drop Down List

May 22, 2015

I have a spreadsheet that has been downloaded from a website. On the website people have chosen from a group of 28 checkboxes their answer. I have imported this spreadsheet into access. I now need to be able to run a report that shows only the one they chose instead of listing all 28..

View 1 Replies View Related

Queries :: Using Variable In A List Field Query - Getting Complete List On Initial View

Mar 28, 2014

In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.

So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.

I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.

VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);

VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery

The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.

Any hints, how I have to use the * for getting the complete list on the initial view ?

View 5 Replies View Related

List Table, Limiting Field List..

Oct 14, 2006

Okay,

The subject is probably confusing but I'll try to explain.
Setup:

Table1
FieldID = Number
FieldName = Text

Table2
FieldID = Number
FieldName = Text
T1_ID = Number

Relationship
Table1.FieldID 1-> * Table2.T1_ID (one to many)

Now, I'm trying to create a 3rd table that has field populated by Table1 and a field populated by Table2 but I want to limit the field populated by table2 by what is selected in the field populated by table1.
Table1:

1st
2nd
3rd
4th

Table2:

1st,January
1st,February
1st,March
2nd,April
2nd,May
2nd,June
3rd,July
3rd,August
3rd,September
4th,October
4th,November
4th,December


Table3:
Table1Field = Number, ComboBox-Source: Table1, BoundColumn=1
Table2Field = Number, ComboBox-Source: Table2, BoundColumn=1

If Table1Field = 1st then
Table2Field Will only allow January, February, or March as selections, since ONLY they are a member of the "1sr" group via the table1 list.

Is this possible, or do i have to do some run-time checking or something?
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

View 1 Replies View Related

Group By More Than One Field

Feb 4, 2008

Hi!

I would like to know how GroupBy works. I have a 1-many relationship (invoice-products)

I have this query:

[OPERATION_ID], DESCRIPTION: [PRODUCT NAME] & [PRODUCT TYPE], [QUANTITY]

What I would like is to have a group by DESCRIPTION since some products may be repeated in a same OPERATION cuz they are from dif origin but in the invoice they gotta be merged.

So if I have:

1 | GARLIC WHITE | 20
1 | GARLIC WHITE | 20
1 | ONION RED | 20
2 | GARLIC WHITE | 20
2 | POTATOE RED | 20

The query should return

1 | GARLIC WHITE | 40
1 | ONION RED | 20
2 | GARLIC WHITE | 20
2 | POTATOE RED | 20

With this I mean: If DESCRIPTION (product and product type) are the same, group them Per OPERATION_ID and Sum QUANTITIES

View 4 Replies View Related

Reports :: Group By Status Field

Jul 11, 2013

I have a working report which groups by a status field. Let's say I have Status (AA, BB, CC, DD, EE). It is grouping and summarizing fine. I would like to be able to merge some groups. So I would like to be able to Group on (AA/CC, BB, DD/EE).

What would be the best way to approach this?

View 4 Replies View Related

Forms :: Subform - Group On Certain Field

Jul 17, 2013

Using access 2010. I created a subform but now I wish to group on a certain field. I looked and all gives me header and footer. Theres not a option to group. I tried putting the field I wanted in the header section and it still doesnt group.

View 1 Replies View Related

Concatenate Field Or Group By In Report

Dec 15, 2012

I'm looking for a way to summarize multiple fields of data from the same column in a single field. I've seen this page but have been unable to get it to work. I save the code listed but do not know which of the example fields would relate to my table.

Code:
SELECT CompanyName, ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
FROM tblCompany;

I saw that I can create a report and select "group by," but then all the columns appear on different lines rather than summarized on one line. Here is an example of how I would like the data to appear, separated by commas or with specific dates within parentheses. The reason I want them all on one line is to provide an easier to read/more succinct summary.

e.g.
ITEM
LOCATION
DATE

GORP
EAST
1/1/2012

GORP
EAST
2/1/2012

[code]...

View 7 Replies View Related

Sequential Number By Group In A Field?

Jul 19, 2015

I have a table like the one below. I need to Auto Sequence based on JobIDOne in the field that says sequence in the manner that I've typed. Hand typing is not an option because by table will be updated regularly. All other fields are updated via a form. I need a either a macro or VBA solution that can reconcile this, preferably through the table. Note, I do not want to use a query to create this sequencing or SQL language as I do not know how to write SQL commands.

I know it can be done but I've seen about a thousand ways to do it that I haven't been able to modify for my specific table.

DateOne
TimeIn
JobIDOne
InitialsOne
Dates Worked One
Sequence

7/17/2015
6:45:10 PM
12345
AR
7/17/2015
1

[code]....

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

Queries :: Group By Part Of Field In Query

Jun 26, 2015

I am trying to group records in a query and count them. I have records containing ABC12345

ABC67890
ABE12345
ABE67890

Basically the third letter is what I need to group on so that I can count the number of records with ABC, ABE and any other variant of the third letter but with the numbers all varying all over the place.In the example above I want to find ABC 2 and ABE 2.

View 3 Replies View Related

Queries :: MAX Date Of Records - Add Another Field But Don't Want It To Group On It

Oct 28, 2014

I have a query that feeds another query. The first query needs to carry the Primary key, a secondary key (grouped by) a date (Maxed on) and an operator key (to group on) ...

At the moment I cant include the primary key because it groups on it and therefore every transaction is reports, my sql is -- How can I include the primary key MatterContactsMadeId and still get the max date for an operator within a matter

Code:

SELECT MatterContactsMade.MatterId, Max(MatterContactsMade.DueDate) AS MaxDueDate, MatterContactsMade.Operator
FROM MatterContactsMade
GROUP BY MatterContactsMade.MatterId, MatterContactsMade.Operator
HAVING (((Max(MatterContactsMade.DueDate))<=[Forms]![MattersBUpLogOptionsFrm]![txtBUpDate]) AND ((MatterContactsMade.Operator)=[Forms]![MattersBUpLogOptionsFrm]![txtOperator]))
ORDER BY MatterContactsMade.MatterId;

View 1 Replies View Related







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