Grouping Two Tables Which Have Different Number Of Data

Sep 23, 2007

Hi All,
I have two tables

Table A

EmpID EmpName
001 Will
002 Jack
003 Emma
004 Greg

Table B
EmpName EmpHobby
Will Sport
Jack Reading

I wanna join the two table by using query so the result will be

EmpID EmpName EmpHobby
001 Will Sport
002 Jack Reading
003 Emma (blank)
004 Greg (blank)

what query should I write

Kindly need your help,i am frustrating with this

View Replies


ADVERTISEMENT

Queries :: Grouping Number Field Into Intervals In A Query

Jul 2, 2014

I have a number field called Jobs.SquareFootage, and I want to run a query that categorizes the field into intervals. The intervals I want are:

0 to 50 squarefeet
51 to 300 squarefeet
300+ squarefeet

This is the query I'm coming up with so far

JobID SquareFootage Revenue
1 40 $5
2 30 $10
3 200 $400
4 250 $350
5 550 $600

I want to put the SquareFootage into the intervals I mentioned above, and get the total revenue for each interval.

View 5 Replies View Related

Tables :: How To Get Data From Crosstab When Click On Number

Mar 24, 2014

I have a query of people and their position, I did a crosstab query and now I have numerical data. How so I get it to show me the data when I click on the number?

View 1 Replies View Related

Auto Fill Data Number Between Tables

Feb 7, 2014

I am creating a Access Data Base for Product Complaint. I have created several tables that share an auto complaint number (Primary Key). How do I make that auto assign? Below is the format of my Complaint number;

A Complaint Number is assigned using the format: PCYYMMXXX
Where:
PC = Prefix indicating Product Complaint
YY = Last two digits of the year when complaint originated
MM = Two digits for the month when complaint originated
XXX = Sequential number starting at 001 for each year

View 1 Replies View Related

Table Fields (Name / Number) Dependent On Another Tables Data.

May 18, 2006

I have an ... interesting issue. I am wondering if others have dealt with this. I have a solution, but am not the happiest with it.

I basically have 2 databases here. (Or I will when I'm done making them) and one is dependent upon the other. In fact, the 2nd's tables are dependent upon the fields in the first database's table.

This database is for quality control checking purposes.

First off,

Database 1:
Basically a list of standardized audits and what needs to be checked for those audits. What needs to be checked can change as things progress, but stays pretty standard.

Database 2:
This will hold the checks that the QC branch will do, based on what type of Quality Check they are doing, they have an audit standard. This audit standard exists in database 1.

What basically ends up happening is that the data in Database1 become Table fields in database2.

Example:

Database1:
Radio Check Audit Standard.
Needs to check following:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.

Another Random Check Audit Standard
Needs to check the following
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Did something specific for this Audit
Didn't do something else, also specific for this Audit.
Etc.

Continue on with other Audits.


Database2: Will keep track of all the quality checks that have been preformed.

Today we will check Radio Protocol
Did they:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.

Yes, No as necessary.

2 Days later, we need to do another QC on the Radio Protocol
Need to check if they did:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.


Etc.


But what will be checked for in Database2 depends on what the standard is for the "Radio Check Protocol," or etc.

Right now, i see 2 options:

Option1
Make the 2 databases, and when a user "Picks" the Radio Check protocol, a linked table is filled with the Name / object that needs to be checked with a field in the same row to hold the data if the object was done / not done etc.
The fields in Database1 are automatically put into this specific instance of Quality Check, etc.

Only problem with this: Requires coding to auto update. Easy to do, but if errors occur, things will screw up.

Option2:
When the user picks the Radio Check protocol, simply allow a subform with a refreshed drop down box. Allow user to pick items they need (Limit it to only this Audit list, etc) to set to true. Assume false for other items not specifically chosen by user.

Problem with this: If an Audit standard changes to remove an item, or add item. Any quality checks done will also change for the assumed false items. If another Item added in, then all old QCs will now also have that item added in / reported assumed false. And if it is deleted, all old QCs will loose the item that was reported false.


I'm seen the first option as the only viable one.

Am I missing this, is there some other way to do this that would be better / wiser? Etc?

Kelemit

View 14 Replies View Related

Tables :: Access Returns Number In Data Type

Jan 16, 2014

I created several tables that contain the look-up data I want to post to database which I will use as the repository for a SharePoint form.Users visiting SharePoint site will enter the data to be kept in an Access database so we can create reports (not seen by the user)

Problem: When I created the database I linked fields to tables to create the look-up lists using the wizard.

When I saved the database, the first field that I linked returned a value of "number" instead of whatever the default value should be for a look-up text field.

View 7 Replies View Related

Tables :: Change Client ID Data Type From Text To Number

Mar 27, 2014

I have two tables(see below). I want to set up a query, link these 2 tables together. I set a one-to-one relationship between Client ID in two table. But got error message :"Type mismatch in expression".

I tried to change Client ID data type from "Text" to "Number", then Access deleted some data under Client ID in Order table.How can I make this work, but not having to re-type in all data?

Client Table:

Client ID(Autonumber)
Client Name (Text)
Client Address (Text)

Order Table:

Order ID(Autonumber)
Client ID(Short Text)
Unit Order(Number)
Unit Price

View 3 Replies View Related

Tables :: Data Conversion - Short Text Fields To ID Number

Jun 15, 2014

I have an old Table with Movies, Actress, Actor, & Director Fields & I have converted it to .accdb. I want to turn those short text fields to an ID number. I have built Tables for those fields(indexed no dups) with an ID field. I have over 5000 records in the original table and dont want to input those numbers by hand. I am using Access 2013.

View 5 Replies View Related

How To Reset Tables To Change Data Type From Number To Autonumber

Dec 4, 2013

So I have decided that I want my ID's to be AutoNumbers, but at the moment they are currently set as Numbers. I have already inserted data, to test, which has been deleted, however I am now unable to change the ID field back to AutoNumber.

How can I duplicate the tables so that this field can be changed again?

I have like 10 tables with heaps of feild, so remaking them will take long, but I know there is a way using queries, I am just not sure how...

View 2 Replies View Related

Tables :: Choose Number From Field1 To Automatically Fill Field2 With Appropriate Data

Jun 30, 2015

I have 2 fields in access table. In one field i fill numbers eg (1,2,3,4,5) drop down list. In the second field I need to fill another data. When I chose one of the numbers from Field 1, I want automatically fill the second field with appropriate data.

1 = 24857
2 = 24869
3 = 24899
4 = 24944
5 = 24994
6 = 24903
7 = 15480
8 = 15164
9 = 15482
10 = 15479
11 = 15468
12 = 15476
13 = 15489
14 = 15494
15 = 7524
16 = 7537
17 = 7523
18 = 7544
19 = 7533
20 = 7536
21 = 7539
22 = 7534

View 2 Replies View Related

Grouping Data

May 16, 2006

How do i group the following records

ID Surname Pack
29679Minican 1
29679Minican 2
27818Oliver 1
27818Oliver 2
27818Oliver 3

so its will show ...

27818Oliver
29679Minican

... on a spreadsheet

View 1 Replies View Related

Grouping Of Data

Jul 6, 2006

I have a table which stores meeting room booking information in half hour slots.
If someone books a 2 hour meeting then 4 records would be produced – one for each half hour. I want to produce a query which will group the data by room (ScheduleID) showing the max and min times (ie the initial start time and final end time) for each event and the event details. The table structure is as follows

ScheduleDetailsID, ScheduleID, CustomerID, ScheduleStartTime, ScheduleEndTime, meeting purpose

And the query I have tried is as follows:

SELECT [Schedule Details].ScheduleDetailsID, [Schedule Details].ScheduleID, [Schedule Details].CustomerID, Min([Schedule Details].ScheduleStartTime) AS MinOfScheduleStartTime, Max([Schedule Details].ScheduleEndTime) AS MaxOfScheduleEndTime, [Schedule Details].[meeting purpose]
FROM [Schedule Details]
GROUP BY [Schedule Details].ScheduleDetailsID, [Schedule Details].ScheduleID, [Schedule Details].CustomerID, [Schedule Details].[meeting purpose];

Can anyone tell me where I have gone wrong. It does not group the data as I want it to ie by room, then time, with only the initial start and final end times. Thanks in advance. Peter

View 3 Replies View Related

Data Grouping

Jan 14, 2008

Hi, Last piece of advice i got from here was excellent so thought i would try it again,

I have records that show delivery days and postcodes some post codes have more than 1 item going to them on several days through the week i was wondering if i could group the same postcodes together so it only showed 1 record instead of a possible 15 but only those delivered on the same day, Is this possible?

View 3 Replies View Related

Tables :: Converting Text Data Type To Number Data Type

Nov 3, 2012

I have a table with a field with names set to text data type and i want to change it to number data type but when i do it in design view the data get lost. I want to know if there is a way to convert the data in the field as number type and keep the data in the field.

View 7 Replies View Related

Reports :: Grouping A Report With 2 Fields From 2 Tables

Nov 8, 2014

I'm trying to create a report with data from 7 different tables. It's supposed to be an assignment overview for a transfer company (driving people from A to B, dunno how to call it in English). I put assignment and customer details into the page header, which is working out fine so far. But now I want to show transfer details and it's proving to be a bit troublesome.

When creating the assignment, the user can choose whether the transfer goes from/to an address or from/to an airport. Depending on which one he chose the data is stored in different tables. To determine whether it is an outward journey/collection, outward journey/target, return journey/collection or return journey/target, I put a field in each of those tables ("Schritt", its value being either 1, 2, 3 or 4).

To visualize I made screenshot of the tables in relationship view, but since I'm a new member i can't seem to post images, so I'm giving you a pseudo URL...

[abload (dot) de (slash) img (slash) transeren40p1r.png]

The final report should look like this:

outward journey
---collection (1)
---target (2)
return journey
---collection (3)
---target (4)

Having the "Schritt" value for each step in either one or another table (address(es) or flight data) is making it a bit hard for me to wrap my head around the problem. Is that doable with expressions or is there a way in VBA to solve this problem?

View 7 Replies View Related

Grouping Daily Data By Week

Nov 2, 2005

Hi all,

I currently have a table that holds 2 months of data. Let's just use January/February 2004 as an example.

The field name is [DateReceived] and goes from '1/1/2004' to '2/29/2004'. I need to group my records in 7-day spans for a future query, so anything from 1/1 to 1/7 would be grouped, etc.....

I have no idea how to do this. Any help would be great.

sanctos

View 2 Replies View Related

Grouping: Data Access Pages

Dec 18, 2007

G'day,

Putting together some data access pages to display data from tables.

All is good except a property that I do not like, when there are multiple records in the returned recordset from the query to the page, I have grouped these by ID (for example).

Each time you move to next record the display contracts back to just the top level of display, that is with the the ID number displayed in a text box and a "+" to expand it out to see the details of the record under that group.

I would like have it always expanded to show the details of the records as I scroll though the ID numbers (the top level of grouping), rather than having to expand for each ID number to see the details.

Not sure that is clear, but any ideas..........

View 1 Replies View Related

DLookUp, Dates And Grouping Data

Jun 10, 2007

Hello!

Is it possible to group data in form from table within two dates with aggregate function DLookUp,

Thanx

View 1 Replies View Related

Forms :: Grouping Data Within Combo Box

Aug 15, 2013

I have a list of customers, with Account Numbers and Company Names. Now I have entered a combo box in my form control with a list of Account Numbers, how ever I wish to group these Account Numbers because some are the same, with the same company but a different Employee.

So how do I group Account Numbers within the combo box?

View 4 Replies View Related

Grouping Data Into Sections From A Field?

Oct 10, 2014

I have a database with a list of clients. One field is for their date of birth.

I have to report on equality and need to know how many clients we have within the following age groups:

16 - 24
25 - 34

35 - 44
45 - 54
55 - 64
65+

I have looked through the group and sort functions and cannot see anything obvious.

View 2 Replies View Related

General :: Grouping Records - Query To Gather Information From Tables

Mar 7, 2013

I have a query that gathers information from the tables in my database and returns something like this:

Code:
Field Pest
1 Insect1
1 Insect2
1 Insect3
2 Insect1
2 Insect3
2 Insect4

Field and Pest are fields in two separate (but joined) tables. Field exists only once in its table, and multiple pests are attributed to each field. I'm looking to create a table or query in which the values would be listed as follows:

Code:
Field Pest
1 Insect1, Insect2, Insect3
2 Insect1, Insect3, Insect4

The purpose for this is to create a legend for a map used by another program based on the information gathered in the database. I do have other criteria for which fields and pests show up in this query.

View 11 Replies View Related

General :: Grouping Data To Send To Email

Jul 11, 2012

i have a filter that shows the bookings for the weekend, which i can click on each act and it generates an email for then to confirm. however some acts have more than one booking on that weekend and i want to be able to list the bookings in one email, the email script i use at the moment is below, the acts can be grouped by a field called grouped

Code:
Private Sub artist_DblClick(Cancel As Integer)
Dim msgTxt As Variant
Dim objOutlook As Outlook.Application
Dim objMailItem As Outlook.MailItem

[code]....

View 1 Replies View Related

Queries :: Grouping Data By Week And Display

Mar 19, 2013

I am trying to create a query that will group my data by each week so I can sum up some numbers and display them on a per-week basis... Prefer the week to start on Sunday but really not picky about it.

Using the following in the query design window.

Week of Year: Format([Date],"ww")

This is returning:
1
10
11
12
2
3
4
and so on.. See the problem?

I tried sorting but that doesn't work.

I also tried the following:

Format([Date],"mmmm,ww")

But this is returning
December,51
December,52
February,8
February,9
January,1
January,2
January,3

Yes, there are some weeks without data but that's not the problem. The issue is that its putting February before January.. Why is this? Again, I tried sorting options and several other techniques but no success.

View 5 Replies View Related

Reports :: Grouping Report Data From Start To End Date?

May 31, 2014

Grouping a report data starting from date 01.04.2013 to 31.03.2014 ??

View 2 Replies View Related

Reports :: Group Data In A Report From Single Table Using Grouping And Sorting

Dec 2, 2014

I'm trying to group data in a report from single table using grouping and sorting and I want the percentile of every record over group total. I'm using a query to fetch data from table, however I'm unable to get percentage of every single record over group total.

I want to display the report as attached image in single report. I'm unable to get data in "Perc" field. It's populating wrong values.

View 3 Replies View Related

Queries :: Pull Data Values In Array To Become Grouping Headers In A Report

Apr 23, 2014

I have a table with application records. One of the fields captures schools the applicant will work at. This field stores data in comma delimited format. There could be 1 school name; there could be 5 school names.

My ultimate goal is to build a report which shows me records of all applications, grouped by school choice. I want to see: School A was selected by 5 people, School B was selected by 7, etc.

Is it possible to write a query from this table that will enable this?

View 4 Replies View Related







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