Make Table Query 01-04-08

I am trying to post new data to a table every week and apply an autonumber to 
rank the data.  How can I apply an autonumber to the new data after my delete 
query has cleared out the previous data?  Automatically, of course.  I would 
like my macro to be able to delete and post the new data by rank, so that 
reports can be generated as well.
0
Utf
1/4/2008 6:54:03 PM
access 16762 articles. 3 followers. Follow

5 Replies
809 Views

Similar Articles

[PageSpeed] 56

Adam

Perhaps you and I don't share the same definition of "rank".  To me, this 
term implies some inherent order.

Is this how you use the term?

What will having a "rank" for each record allow you to do that you couldn't 
otherwise do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Adam" <Adam@discussions.microsoft.com> wrote in message 
news:E3AB2C58-A849-4CD2-88C9-4103FFE515D7@microsoft.com...
>I am trying to post new data to a table every week and apply an autonumber 
>to
> rank the data.  How can I apply an autonumber to the new data after my 
> delete
> query has cleared out the previous data?  Automatically, of course.  I 
> would
> like my macro to be able to delete and post the new data by rank, so that
> reports can be generated as well. 


0
Jeff
1/4/2008 7:06:14 PM
The data that I will be supplying in this new table will be sorted in a high 
dollar order.  I am trying to pull out the top 20 dollar amounts out of 
another table.  My theory is: if I query Table 1 and sort -descending, then 
post to a new table and apply an autonumber, the autonumber will serve as a 
rank.  Then I can query this table and filter out all ranks <21.  A report 
based on this query can display the Top 20.  If there is an easier way, I am 
all ears. 

"Jeff Boyce" wrote:

> Adam
> 
> Perhaps you and I don't share the same definition of "rank".  To me, this 
> term implies some inherent order.
> 
> Is this how you use the term?
> 
> What will having a "rank" for each record allow you to do that you couldn't 
> otherwise do?
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> "Adam" <Adam@discussions.microsoft.com> wrote in message 
> news:E3AB2C58-A849-4CD2-88C9-4103FFE515D7@microsoft.com...
> >I am trying to post new data to a table every week and apply an autonumber 
> >to
> > rank the data.  How can I apply an autonumber to the new data after my 
> > delete
> > query has cleared out the previous data?  Automatically, of course.  I 
> > would
> > like my macro to be able to delete and post the new data by rank, so that
> > reports can be generated as well. 
> 
> 
> 
0
Utf
1/4/2008 7:31:01 PM
Adam

You have a mistaken assumption.  Autonumbers are intended for use as unique 
row identifiers.  There's no inherent need for them to be sequential.

If you already have a [Amt] field you can sort descending by amount, then 
why not just use that?  If you need only the top 20, you can use the Top 
property of a query to return only the top ... 20?!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Adam" <Adam@discussions.microsoft.com> wrote in message 
news:8F8E29D9-A63A-4F20-B94A-5BE923E304FE@microsoft.com...
> The data that I will be supplying in this new table will be sorted in a 
> high
> dollar order.  I am trying to pull out the top 20 dollar amounts out of
> another table.  My theory is: if I query Table 1 and sort -descending, 
> then
> post to a new table and apply an autonumber, the autonumber will serve as 
> a
> rank.  Then I can query this table and filter out all ranks <21.  A report
> based on this query can display the Top 20.  If there is an easier way, I 
> am
> all ears.
>
> "Jeff Boyce" wrote:
>
>> Adam
>>
>> Perhaps you and I don't share the same definition of "rank".  To me, this
>> term implies some inherent order.
>>
>> Is this how you use the term?
>>
>> What will having a "rank" for each record allow you to do that you 
>> couldn't
>> otherwise do?
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Adam" <Adam@discussions.microsoft.com> wrote in message
>> news:E3AB2C58-A849-4CD2-88C9-4103FFE515D7@microsoft.com...
>> >I am trying to post new data to a table every week and apply an 
>> >autonumber
>> >to
>> > rank the data.  How can I apply an autonumber to the new data after my
>> > delete
>> > query has cleared out the previous data?  Automatically, of course.  I
>> > would
>> > like my macro to be able to delete and post the new data by rank, so 
>> > that
>> > reports can be generated as well.
>>
>>
>> 


0
Jeff
1/4/2008 8:01:09 PM
I like it.  How do you apply this top property?  

"Jeff Boyce" wrote:

> Adam
> 
> You have a mistaken assumption.  Autonumbers are intended for use as unique 
> row identifiers.  There's no inherent need for them to be sequential.
> 
> If you already have a [Amt] field you can sort descending by amount, then 
> why not just use that?  If you need only the top 20, you can use the Top 
> property of a query to return only the top ... 20?!
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> 
> "Adam" <Adam@discussions.microsoft.com> wrote in message 
> news:8F8E29D9-A63A-4F20-B94A-5BE923E304FE@microsoft.com...
> > The data that I will be supplying in this new table will be sorted in a 
> > high
> > dollar order.  I am trying to pull out the top 20 dollar amounts out of
> > another table.  My theory is: if I query Table 1 and sort -descending, 
> > then
> > post to a new table and apply an autonumber, the autonumber will serve as 
> > a
> > rank.  Then I can query this table and filter out all ranks <21.  A report
> > based on this query can display the Top 20.  If there is an easier way, I 
> > am
> > all ears.
> >
> > "Jeff Boyce" wrote:
> >
> >> Adam
> >>
> >> Perhaps you and I don't share the same definition of "rank".  To me, this
> >> term implies some inherent order.
> >>
> >> Is this how you use the term?
> >>
> >> What will having a "rank" for each record allow you to do that you 
> >> couldn't
> >> otherwise do?
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >> "Adam" <Adam@discussions.microsoft.com> wrote in message
> >> news:E3AB2C58-A849-4CD2-88C9-4103FFE515D7@microsoft.com...
> >> >I am trying to post new data to a table every week and apply an 
> >> >autonumber
> >> >to
> >> > rank the data.  How can I apply an autonumber to the new data after my
> >> > delete
> >> > query has cleared out the previous data?  Automatically, of course.  I
> >> > would
> >> > like my macro to be able to delete and post the new data by rank, so 
> >> > that
> >> > reports can be generated as well.
> >>
> >>
> >> 
> 
> 
> 
0
Utf
1/4/2008 8:11:05 PM
Adam

Open the query in design view.

Right-click in the open space above the grid.  Select Properties.  Pick the 
Top Values property and set it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Adam" <Adam@discussions.microsoft.com> wrote in message 
news:9D04DBF3-E6A6-456E-AE97-65355CFA566E@microsoft.com...
>I like it.  How do you apply this top property?
>
> "Jeff Boyce" wrote:
>
>> Adam
>>
>> You have a mistaken assumption.  Autonumbers are intended for use as 
>> unique
>> row identifiers.  There's no inherent need for them to be sequential.
>>
>> If you already have a [Amt] field you can sort descending by amount, then
>> why not just use that?  If you need only the top 20, you can use the Top
>> property of a query to return only the top ... 20?!
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>>
>> "Adam" <Adam@discussions.microsoft.com> wrote in message
>> news:8F8E29D9-A63A-4F20-B94A-5BE923E304FE@microsoft.com...
>> > The data that I will be supplying in this new table will be sorted in a
>> > high
>> > dollar order.  I am trying to pull out the top 20 dollar amounts out of
>> > another table.  My theory is: if I query Table 1 and sort -descending,
>> > then
>> > post to a new table and apply an autonumber, the autonumber will serve 
>> > as
>> > a
>> > rank.  Then I can query this table and filter out all ranks <21.  A 
>> > report
>> > based on this query can display the Top 20.  If there is an easier way, 
>> > I
>> > am
>> > all ears.
>> >
>> > "Jeff Boyce" wrote:
>> >
>> >> Adam
>> >>
>> >> Perhaps you and I don't share the same definition of "rank".  To me, 
>> >> this
>> >> term implies some inherent order.
>> >>
>> >> Is this how you use the term?
>> >>
>> >> What will having a "rank" for each record allow you to do that you
>> >> couldn't
>> >> otherwise do?
>> >>
>> >> Regards
>> >>
>> >> Jeff Boyce
>> >> Microsoft Office/Access MVP
>> >>
>> >> "Adam" <Adam@discussions.microsoft.com> wrote in message
>> >> news:E3AB2C58-A849-4CD2-88C9-4103FFE515D7@microsoft.com...
>> >> >I am trying to post new data to a table every week and apply an
>> >> >autonumber
>> >> >to
>> >> > rank the data.  How can I apply an autonumber to the new data after 
>> >> > my
>> >> > delete
>> >> > query has cleared out the previous data?  Automatically, of course. 
>> >> > I
>> >> > would
>> >> > like my macro to be able to delete and post the new data by rank, so
>> >> > that
>> >> > reports can be generated as well.
>> >>
>> >>
>> >>
>>
>>
>> 


0
Jeff
1/4/2008 11:52:01 PM
Reply:

Similar Artilces:

Parameter query in Access 2003 report
I want to create a parameter query that asks the user to select one, many, or all values from a field when opening a report. I would like the selection to be available from a list, radio box, or pull down menu to minimize spelling errors, etc. Unfortunately the list is of names which number about 100. Can this be done? If so, could someone please point me to the help pages that describe this process? My solution seems clunky but it is the only way I can see how to do it. Any suggestions as to how else might I best approach this? Thanks in advance Debbie See my sample database that shows ...

Access Denied 04-22-04
you have to add the Domain User group to ability to access the SQL DB, not only while attached to the domain but when you are not, the Database have to be synchronized... ...

a count field in a query?
Hello, I have a query in which I would like to create a field which increments by 1 for each record selected, so if there are 10 records selected by the query, this column would show numbers 1 to 10. Ideally I would like to have some text in front of each number, say invoice1, invoice2 etc. From a previous posting I realise that this is frowned upon but it would be the simplest solution for my problem! How can I do this? Thanks Geoff On Mon, 03 Dec 2007 08:54:00 +0000, Geoff Cox <<>> wrote: >Hello, > >I have a query in which I would like to create a field which >...

Rename Multiple Tables at once
I have an Access 2007 database that has a lot of linked tables and I want to 'batch' rename them. For example, they all start with "dbo_" and I want to rename all of them to "PM_". How can I make that happen? I've been searching and cannot find a solution. Any help that anyone can provide is greatly appreciated. Dim dbCurr As DAO.Database Dim tdfCurr As DAO.TableDef Set dbCurr = CurrentDb() For Each tdfCurr In dbCurr.TableDefs If Left(tdfCurr.Name, 4) = "dbo_" Then tdfCurr.Name = "PM_" & Mid(tdfCurr.Name, 5)...

Table Design Issue
I have a database the has a Parts Table and a Vendors Table. what needs to happen is i need a form that will allow someon to view all the information about that parts itself and the vendors that supply the part but every part is available by all 5 vendors for different cost, and i need to construct tables that when part information is undated or deleted it will reflect across the board and on the form itself be able to see the vendor name and the part information for that vendor. what type of relationships will be involved here Answered in tablesdbdesign. ...

How can I make my document into less mb for email
I have created a document that is 143 pages. It will not send in email. I need it to be a smaller megabite version to send. How can I do that? Create a PDF. Can't guarantee it will be really small, but it should be smaller than a Publisher file. There are free PDF conversion applications around. www.primopdf.com is mentioned here. If you have Publisher 2007 there is an Office PDF add-in. 2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS https://www.microsoft.com/downloads/details.aspx?FamilyId=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en -- Mary Sauer MSFT MVP htt...

Protection 04-19-10
I know its basic but how do i protect a sheet so that when i delete an entry i dont delete the formula? Thanks James All cells on a worksheet are "Locked" by default. When you apply Protection to the sheet, with or without a password, you can no longer make changes to Locked cells. So begin by selecting and using Format --> Cells --> Protection to "Unlock" the cells you DO want to be able to make changes to, as in deleting some user input. Leave the ones with formulas in them locked. Then apply Protection to the sheet. You can select more t...

Pivot Tables #32
Hi - I have got to learn fast how to make pivot tables from a database in Excel 2003. I know how to set up the database, however I just don't seem to grasp the logic or procedure to set up the table. Is there somewhere on the web that I can go do get almost a step by step explanations and examples to make them clearer? Thank You There are instructions and links for pivot tables on Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivotstart.htm sue t wrote: > Hi - I have got to learn fast how to make pivot tables > from a database in Excel 2003. I know how ...

Pivot Table Report: Getting average of difference of two date columns
Hello I have data in excel sheet as: WORK_ID PROJECT START_DT END_DT 1001 P1 1/1/2006 1/10/2006 1002 P1 1/25/2006 2/20/2006 1003 P1 3/15/2006 3/20/2006 I want to have a report that will give me average time taken to finish a work in a project. That is, I have to take average of (end_dt - start_dt) for a project group. How do I do this in a pivot table report. When I use a calculate field to get the days worked for a work_id using DATEDIF(START_DT, END_DT, "D") then excel computes only sum on that (pivot table report ...

Downloading trouble from TD Waterhouse into Money '04
Is anybody else having trouble downloading transactions from TD Waterhouse into Money 2004? I've been doing it for years and it stopped working on Friday when I was also having trouble running Money updates. Those now work but Waterhouse downloads will not. It tells me there is a problem with the data downloaded, contact the bank... I've been working with Waterhouse since Friday and so far it still doesn't work :( ...

CRM customization query
Hi, I have a customization query, I want to see the number of records available before I am getting the data for reports. I need this because, I want to generate a report by applying some filter criteria but at the same time I want the resultant record number below a specific limit. On the advance find, after I am applying the filters we get hte resultant data by clicking on the Find button. This takes us to the page where we can see the data. Now I want to add a new button on the Advance find screen on click of which I can get the record count of the result set for the filter i have ...

Pivot Tables #16
What are the point of Pivot Tables? I was asked about it at work, and I have no idea what they are and how useful they are? Very useful, you could have a look here to make a start, http://www.ozgrid.com/Excel/excel-pivot-tables.htm Regards, Alan. "Jason Woolsey" <jasonmwoolsey@me.com> wrote in message news:72F80D21-15FD-4B00-B934-FE2A46EACC3B@microsoft.com... > What are the point of Pivot Tables? I was asked about it at work, and I > have no idea what they are and how useful they are? Hi Jason They are the hidden gem in Excel. A fantastically easy way of ana...

m_strFilter and multiple tables.
Hi, Can I specify a table list in a m_strFilter statement? ie: How do I accomplish the following using m_strFilter: SELECT * FROM RS1,RS2 WHERE RS1.DOCN = RS2.DOCN Thanks. ...

Editing Table of Authorities Categories
Heres a good one to chew on... There are occasions when we want to separate out the Statutes category into two separate categories, "federal statutes" and "state statutes", because Word doesn't recognize that federal statutes must appear before state statutes, and we don't want to run the risk of manually moving the federal up before the state and then updating the table of authorities and forgetting to do the manual work again. Here's my issue: I have macro that reassigns categories 2 through 8, essentially renaming Statutes and Other Authoriti...

How do I make cut-out of an animal separate from the background?
I am trying to cut-out an animal so all I have of the picture is the cut-out of the animal with no background. I used to be anle to do this on the old Publisher... Delphi wrote: > I am trying to cut-out an animal so all I have of the picture is the > cut-out of the animal with no background. I used to be anle to do > this on the old Publisher... ============================== Insert the picture and...on your Picture Toolbar...go to...Text Wrapping / Edit Wrap Points. Now you can place your pointer anywhere along the edge of the picture and drag the nodes to the position you require...

including dblink in a query to retrieve data
I connect to a table using a db link i want to execute an spl query and populate results in to a pivo table. My MS Query does not allow me to use a dblink and connect to th table. Please help -- Message posted from http://www.ExcelForum.com ...

How To Align Decimals In A PP 2007 Table
I have financial data with one decimal point where I want to align to the decimal point in a PowerPoint table. How do I select the full column and align to the decimals? Thanks! (B^>)-]=[ You can't. I've asked for this feature ever since about PowerPoint 2000, but it's still not there. Frustrating. You have to do this individually in each cell. Basically, choose the decimal tab option to the left of the ruler, then add a tab (Ctrl+Tab) before the text in each cell as necessary. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT ...

Database degrag query
Hi , I am using exchange 2003 with SP1. I have calculated the total size of mailboxes , it is total 30 GB. But the size if edb file is 50 GB and stm file is 25 GB . I have defraged the database offline so that it will purge the white space if there is any. but after defrag no difference in size of the database. it is still 50gb edb and 25 gb stm file. can anuone explain if i have only 30gb total mailbox size then why it is taking so much space. there is no rules and othere things define. Waiting for your help. Regards, Abhi. On 25 Oct 2004 20:45:43 -0700, pandeyabhishek2000@yahoo.com (abhi...

Union Query with division
Hello, it would like to join two tables, Table1 and Table2, however when joining table2 the items listed in that table need to be divided by 2. I normally do this division in a query then join that query to the union query with table1. I wonder if I can accomplish the same thing directly from the union query only. Also, is there a way to rename a customer ID with the same query? (See Customer 9 as example, changing to Customer 15) Example: Table1: [Customer] [Hours] Customer 1 10 Customer 3 5 Table2: [Customer] [Hours] Customer 9 ...

Using Min to Calculate across a table
I am attempting to massage data so I can see what the maximum number of pieces I can produce based off of various components. My query to figure out how many of each component is available per unit is complete and the output is: Part_Number, MaxComponent1, MaxComponent2,MaxComponent3, MaxComponent4 Does anyone have any suggestions as to how I could find the MIN of the components and group by part number? Cheers See the MinOfList() function here: http://allenbrowne.com/func-09.html A better solution would be to create a related table with many records for the combinations that are va...

Save query result in a variable
hi! hope you can help me AGAIN! i have a form that let's the user pick a name, job, between dates, tax(with or without) and percentage. this is for calculating the comision each employee has earned. i did this by a query that gets the info from the form adn then opens a report and in the report i make the calculations. but it turns out that several employees have 2 or more "jobs" and for each one diferent % of comission. the form and the reports handdle it fine, i'm looking for a way to add all of this reports for each person. i was thinking of saving the sum of each que...

Query to count between list of number (Predicting Start/End that may occur in data range)
Hi, I have a below list of numbers. 566667 566668 566669 566665 566666 566671 566672 566680 I want a query that would return a count between start and end of range. Like Start End Quantity 566665 566669 5 566671 566672 2 566680 566680 1 Thank you. On 2 apr, 07:17, Angela <ims...@gmail.com> wrote: > Hi, > > I have a below list of numbers. > > 566667 > 566668 > 566669 > 566665 > 566666 > 566671 > 566672 > 566680 > > I want a query that would return a coun...

Entering the same value into two table fields at the same time
Say that there is a table which has 2 fields (A, B) There is a form [Form1] (data entry type) to enter data for A by `TextBoxA`. There is also one more `TextBoxB` which is Not Visible. My target is to record the value of (entered thtough `TextBoxA`) into B field of the table as well. So I write down a code "Before Insert" of the Form1 that: " Form_Form1.TextBoxB = Form_Form1.TextBoxA " It is not recording A values into the field B... Where am I wrong? Can you help me please? Sory !!! When I write down the same code into `OnExit` of TextBoxA, it is working...!! yes, ...

How to update a specific field from a Find Duplicates querie
I have to update 1 table that has multiple duplications. If i search for duplicate on Field1, Field2, and Field3 all matching, i receive many duplicates where Field4 and Field 5 are different and/or Null. What do i need to do to the query so that Field4 and Field5 will be automatically updated when the duplicate is found? Current: Field1 Field2 Field3 Field4 Field5 Mike Smith 1234 KEEP Mike Smith 1234 DELETE 9012 Desire result: Field1 Field2 Field3 Field4 Field5 Mike Smith 1234 KEEP 9012 (...

Data Modeling:Lookup table and Main table:establishing relationshi
I am working on creating data model from existing database using MS Visio 2007 Profesional Edition. Existing database is w/o PK-FKs & I am working to create relational DB which enforces RI. I have a lookup table which contains language codes,used by main table. The problem ,I am running into, is that these languagecodes(from lookup table) are used by 3 columns in main table. So, I am wondering how can I enforce PK-FK relationship here. As in... language_code from lookup table is PK and it has to associated w/ column(s) existing in main table. Something like following: Lookup Table ...