Queries :: Way To Group Model Numbers Once 1st 6 Characters Are Identical

Jan 30, 2015

In my transaction table, I would like to find out the latest transaction dates of each family models. It sounds a simple Max function can get the desired result. However model number consists of the first 6 characters for the family model and the rest for the versions (variants). The Max function fails to work in this scenario. Is there any other way to group the model numbers once the 1st 6 characters are identical (the rest is ignored)?

View Replies


ADVERTISEMENT

Detailed Record Numbers By Group In QUERIES

Oct 24, 2007

I have a table that looks like this:

caseID
date
value

I want to create a crosstab query that looks like this:

Date1 Date2 Date3 etc etc
caseID value value value


There are 500 caseIDs in my table. Each caseID has up to 5 corresponding dates/values. Therefore I only want 5 dates as the column headings....listed as 1, 2, 3, 4, 5, etc NOT the actual date (as I then end up with hundreds of column headers).

Does anyone know how to create a detailed number by each group (similar to the runningsum feature in Reports)? I know I need to somehow assign each date a "date number" but I dont' know how to.

Any ideas??

View 3 Replies View Related

Queries :: DCount To Add Sequential Numbers By Group Only Works In Some Instances

Jun 24, 2014

I have a large table (>1M rows), and I have searched various forums for a way to add sequential numbers by Group. The query I have works, but since it's a large table, I broke it up, and did everything what starts with A-E, the F-Q, etc, and appended to a new table.

This query works, on anything that starts with the letter D or later... A-C will not work.

Basically, the source table is a list of all combinations of Part_ID and UPC_Code. I am trying to number the UPC_Code field, per Part_ID. There is an AutoNumber field (ID) that is in the table as well.

This is the SQL. Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense.

Code:
SELECT tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code, DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "'")-DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "' AND [ID] > " & [ID]) AS Seq_Num
FROM tbl_upc
GROUP BY tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code
ORDER BY tbl_upc.ID;

The results of this query are that all Part_IDs that start with A through C produces a Seq_Num of 0, but any that start with a "D" or later number correctly - in other words, the first instance of a particular Part_ID is 1, then 2, and so on up to the total count of that Part_ID.

Running Access 2013.

View 2 Replies View Related

Entering Multiple Identical Orders With Sequential Numbers

Jul 12, 2006

Hello all.

I need to be able to enter a number of orders that would all be the same, but they will have different order numbers, in sequence.

I'd like to enter all the information in a form, then have "First Order Number" and "How Many?" fields, and have the system grab the detail, apply it to the order number entered, create a record in the table, then reapply the detail to the next sequential number, repeating the process until it has created the number of orders shown in the "How Many?" field.

I am at a total loss here, and any help would be GREATLY APPRECIATED!!

Thanks!

Tom

View 3 Replies View Related

How To Match 4 Character Word Or Number Or Combination By Identical 4 Characters Word

Apr 11, 2013

How to match 4 character word or number or combination by identical 4 characters word or number or combination in one word have 10 or 15 characters.

I have to two separate tables (Table A and Table B). Table A has one column (Tag No) and Table B has about 15 columns with one column name Tag No as below

Table A

Table B

Tag No

Tag No

2009

ZZZ-2030-DC

2010

ZZZ-2010-M9P

[code]....

They're both in MS Access.I am trying to match 2 tables - columns (Tag No) with join query, but not success. I want to match 4 characters in Table-A with 4 similar characters in Table-B (Tag No) cell.What query is suitable to compare two tables.

View 8 Replies View Related

Queries :: Query Between Dates But With Count Grouped By Model Number?

Mar 18, 2013

I currently have a query of between dates which the user enters, but when I try to get a total count of model numbers it gives totals for each date. I am trying to get a count of model numbers between these dates with the dates excluded in the grouping.

View 14 Replies View Related

Converting Alphanumeric Characters (and Spaces) To Numbers

Jun 21, 2007

In working with a database, I need to convert 39,000 text fields (i.e.: 6002.2 , 2723 , 6004.55 (Notice the spaces)(commas denote next line)) to number fields. The number of spaces is random, so I can't say "cut off the last n number of characters." There may also be characters that I'm not seeing. I'm not too keen on going through it by hand, and I have access to OpenOffice.org Calc (for those who don't know, it's basically a free version of Excel) so I can pull it into there and convert it, but it still won't get rid of the spaces though. Any ideas?

View 13 Replies View Related

Group NUmbers

Mar 3, 2007

I am using an access database and have range of years for customers
and want to group them together. for example:

1990
1991
1992
1993
1994
1998


and what to display them as 1990-1994, 1998.


How do I do this??? Please note that I am an Access newbie....

View 1 Replies View Related

Group Numbers

Mar 3, 2007

I am using an access database and have range of years for customers
and want to group them together. for example:

The table looks like this:

CustID YearID
cust1 1990
cust1 1991
cust1 1992
cust2 1990
cust2 1991
csut2 1993

And want a query that will display it like this:

CusttomerID YearGrouped
cust1 1990-1992
cust2 1990-1991, 1993

These are just examples not the actuall info... Is this even possiable???? Please note that I am an Access newbie....

View 3 Replies View Related

Two Queries Almost Identical Except One Doesn't Work

Nov 23, 2006

I have an access 2003 database which holds data for lorry loads of timber delivered to different places at different prices and by different hauliers who get paid different ammounts.

I have tables that hold the prices for both haulage (sorted by the delivery location and haulier) and product price (sorted by the delivery location and the haulier.

I have two almost identical queries which give the haulage price for a particular load and the product price for a particular load.

Only the haulage prices seem to be returned - i have checked the settings and relationships in each of the tables invlovled and they appear to be that same.

Why would one query return the value and the other not???

View 4 Replies View Related

Queries :: Joining Data That Is Not Identical

Nov 25, 2014

I have a question regarding making a query with joint data that are not identical, as I'm not sure whether it's possible.

I have to look at certain serum levels at te start of a new medication regimen, and serum levels after 5 years.

I have one database with:

Patient number
Start date medication
Start date medication + 5 years
And one database with:Patient number
Date of blood test
Serum level molecule A
Serum level molecule B

What I want to do is make a query where the patient numbers are joined. The problem is that the "start date of the medication" and the "start date + 5 years" are not identical to the dates of the blood tests. E.g.: I have a patient that started medication on 01/March/2006, but he has had blood tests done every 3 months from 28/November/2003 till now, and not on 01/March/2006.

I would need the results of the blood tests (of molecule A and B) at the start of the medication and after 5 years, but if the test date differs by a day, Access already sees they're not identical.

I would like to have access select a blood test date that is as close to the medication start date as possible, within a time frame of e.g. +3 and -3 months. Is this at all possible?

View 4 Replies View Related

Reset Page Numbers For Each Report Group

Mar 25, 2005

I've got an empty pageheader, and a customer group header that repeats on every page. the customer group does not have a footer, and I have a standard agreement paragraph and a signature line in the page footer.

I want to apply a page number to either the top (group header) or bottom (page footer) of my page to reflect the PAGE OF PAGES for the group, not the overall report.

The printed report gets separated into pages for each customer and mailed to them - doesn't make sense to have running page numbers on these - I'd like the customer to be able to see that they're looking at page 2 of 4 of what I mail them, instead of page 202 of 412.

How can I accomplish this?

Sidenote: I ran across a few references to the MS Knowledgebase report samples file (RptSmp00.mdb) during my pre-post answer search. I downloaded - I browsed - I learned a few new tricks. Good stuff.

Slaughter
slaughter at missouri dot edu

View 2 Replies View Related

Queries :: Combine Four Identical Tables In One Query?

Apr 10, 2014

I have multiple databases (one for each department). All 4 databases have an Agents table (tblAgents). Now I am creating another database that uses all of the agents on site. What I want to do is link all the agents tables and combine the values in one query.

Fields are:
UserID (PK)
AgentName
BadgeNumber (Employee Number)
Campaign (Department)
Coach (Supervisor)
HireDate
Status (Active / Leave of absense / Terminated)

The tables are linked to my new database. So logically, I'd think my next step is to query the tables. This is where I'm stuck. How do I say:

Agent = tblAgentsDepartment1.AgentName and also tblAgentsDepartment2.AgentName and also tblAgentsDepartment3.AgentName and also tblAgentsDepartment4.AgentName?

I'll have to do this for each field involved...

View 9 Replies View Related

Queries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers

Aug 12, 2014

I'm having multiple problems with my database like things such as -

i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too

I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?

i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:

Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"

The Query is the one im most concerned about , i can live without a form.

View 14 Replies View Related

Queries :: Hiding Records With Identical Address Details

Sep 11, 2013

I have written a simple query for address label printing purposes, but I do not want to print labels to identical postal addresses.This sounds such a simple requirement but my efforts to write a simple 'criteria' in the Address1 Field have been unsuccessful.

View 14 Replies View Related

Queries :: Comparing Data Differences Between Two Identical Access Tables?

Dec 10, 2013

I have two identical Access tables, for example tblDataYesterday and tblDataToday. The data from tblDataToday has updated data changes in it that are different from tblDataYesterday. I have a maximum of 100 fields to review. I want to compare the two tables and display in a query the differences between the data from both tables.

View 1 Replies View Related

Total Daily Sales Queries By Model/Total

Mar 8, 2008

Hi,

1) I am pretty newbie to this access programming, do forgive me if my questions sounds stupid.

2) Basically I create an application in access capturing or production information for my company. now the top management suddenly wanted whats their main concern:- Total Daily/Monthly, Quarterly, Annual Sales (By Model If possible)

3) I start with daily (Lets don't be too overly ambitious).

4) I try to let user select dates from my calender control and reflect daily sales (in Total & By Model break down) insert into my form.

5) Understand someone told me from my previous post in Calender control I can achieve it either through forms or queries, which is a better way. (in terms of flexibility to change for program maintenance/ scalibility) wise ?

PS: Please forgive my ignorance :o:(

Thanks (In advance) & God Bless.

View 2 Replies View Related

Queries :: Only Identical Data Listed From Tables And Not All Data

Sep 7, 2013

Attached you will find an example with the problem.

The query is only searching identical data between both tables.

I want it to show results from all users, with all the devices each user has.

What am I doing wrong with the query?

View 4 Replies View Related

Can Anyone Verify My Data Model

Jan 13, 2008

Hi I have designed a database for a small organization. It is a ver small database. can any one verify and tell me the mistakes in my design. It hardly takes 20 min for the people like you. This is my first step in designing. I am attaching the zip file which contains dbm file and business requirements. Complete physical data model is there.

especially in "category_tests" table i feel that i have done some thing wrong.

Thanking you all.

View 2 Replies View Related

Filter By Maker And Model

Apr 26, 2005

I am trying to build a table that lists aircraft viewed by plane spotters.

In the “aircraft_spotted” table I have two look up wizards. The first lists the make of aircraft and the second lists the type.

What I am trying to do is filter the models down to the maker.
If I select Boeing I will only get Boeing models and nothing else. The same would apply to Airbus and so on.

I would like to keep this information contained within the table without using a query but if I have to use the query for the model lookup then so be it.

This one is driving me nuts. :)

View 1 Replies View Related

My Current Data Model... (Please Take A Look)

Jun 29, 2006

What's up everyone, I'm new around here and to db design as well...

I'm posting a really rough data model for the first real database I've been putting together.

I'm still in the early stages, and the concepts behind db design are still pretty foreign to me, so there are bound to be many errors I hope to iron out ASAP.

One specific question:

On all of the data models I have referenced, the "OrderDetails" table has no primary key, can someone help me to understand this? Or explain how it is wrong... Thanks

I was hoping some of the experts and advanced users around here could take a look at this and help steer me in the right direction.

Thanks!!!!

View 3 Replies View Related

How To Model A Field That Manifests Itself In Two Ways

Dec 1, 2005

Here is my problem:

Say I have a products table:

PRODUCT
========
Prod_id
Prod_name
Dept
Type
:


It is now possible that some of the products can also be a product
group, and a product group would then consist of multiple products.
(Note: if a product is a product group as well, both of them have
the same names. therefore for consistency, if in future someone
alters the product name, that should be reflected for the product
group if that product happens to be a product group as well)

So, I started of by thinking that the products table should contain
a binary filed like "Is_this_a_ProductGroup", where for any product
that is also a product group, while creating or editing the product
the user would flag this field "Yes" and "No" otherwise.

S, my revised products table is like:

PRODUCT
========
Prod_id
Prod_name
Is_it_a_ProductGroup
Dept
Type
:


Based on some earlier suggestions in this forum, I tried to create a
junction table like

PRODUCT_PRODGROUP
=================
Prod_id
Prod_Group_id

and joined the Prod_id above to the Prod_idin PRODUCT table and
created a copy of the PRODUCT table and joined the Prod_Group_id to
Prod_id in the PRODUCT_Copy table.

I would like some help now on:

1. whether this design is really correct?

2. If so, how does one go about the process of data entry through
forms for these tables?

Any help is much appreciated. thank you

View 1 Replies View Related

Queries :: Command To Select First 30 Characters

May 7, 2013

I have the command to select first 30 characters in a simple select query.

View 3 Replies View Related

Queries :: How To Remove First 7 Characters In A Field

Dec 24, 2013

explain me the Query Expression to remove the first 7 characters in a column, to rum a Query.Eg - I have entry like - 4/8/11-Pipe...I need to return the result like "Pipe".How to build the query.

View 5 Replies View Related

Queries :: Replace 2 Characters In String With 0s

Dec 8, 2014

I'm familiar with writing a replace query to replace characters or strings but what I'm trying to do this time is a bit unique to me. I have a string of numbers that will either be 8 or 9 digits in length. The first 1 or 2 digits will be the State code (1-50 hence the discrepancy in number of total digits), then a 2 digit agency code, then the last 5 digits are a producer code. What I need to do is change the 2 digits for the agency part both to 0. So basically characters 6 and 7 if you're counting from the right. To me it feels like I'm trying to do it backwards so I'm having a hard time writing it.

View 12 Replies View Related

Queries :: Extract Text Between Certain Characters

May 14, 2014

I have a field that contains the following data:

FirstName:Pedro LastName:Campos Restaurant:BI Strand Location:North Month:March

I need to extract the data into different fields like this:

Field1: Pedro
Field2: Campos
Field3: BI Strand
Field4: North

How to extract the information I want into different fields

View 1 Replies View Related







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