Deleting Lines

Jun 18, 2007

Let's say we have a table:

Sipping branchReceiving BranchProduct #Qty to ship
a c PR1 43
a d PR1 25
a k PR1 1
....

Now I want to delete all lines that have same product code and shipping branch as a privous line.
So the only max amounts for each product and each shipping branch will remain.

How to delete those lines?

Thank you.

View Replies

Deleting All Related Sub Records When Deleting The "main" Record.

Apr 12, 2008

I have two tables.

1. Persons (list of persons)
2. Job history (list of jobs)

each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.

how can i do this? vba or simple properties options?

- Roy

View 1 Replies View Related

Count Lines

Aug 16, 2007

Hi there,

LEts say I have a table

branch item $COGS

we have lots of same items having different $COGS in different branches and all of them are on one table

I sorted that descending by $COGS.

Now I want to pick for each item only 4 first lines having bigger $COGS.

The rest of the lines should be deleted.

How I can do that?

Thanks

View 12 Replies View Related

How Add Lines To Table?

Oct 20, 2006

Hi. (I already ask how to delete rows but it looks too hard to make so I invent new idea)
How I can add lines (INSERT INTO) into table so that Access does not add lines which already are in the table??

View 3 Replies View Related

Two Lines In A Message Box

Mar 3, 2006

Is it possible to create a message box with two lines?

Also I want to make a reference to the valuein a text box in the message box.

This is my code at present:

If MsgBox("ARE YOU SURE YOU WANT TO DELETE THE WHOLE EXERCISE?", vbYesNo + vbQuestion, "Warning") = vbYes Then
CurrentDb.Execute "DELETE FROM ExerciseNametbl WHERE ExerciseName = " & Chr(34) & Me.ExerciseName & Chr(34), dbFailOnError
Me.ExerciseName.Requery
Else
Exit Sub
End If

What I would like is:

"Are you sure you want to delete (The value of comboExerciseName)"

View 3 Replies View Related

List Box - Possible More Lines?

Sep 5, 2006

Hi, sorry my english, but I will try to explain what I try to do.
In my database I have a form, where is a listbox. In this listbox are names of documents. Problem is that the name of each document is so long (two or three lines on monitor) so it is not possible to fit in, because listbox has always only one line for each document.
It is possible to set up listbox to get more lines for each item ?
I will be very happy if somebody help me with it. Thank you very much.
Jirka

View 4 Replies View Related

Blank Lines

Aug 17, 2004

I am working with an access table populated from an excel spreadsheet due to its source if a person has more than one line of data the fileds are left blank.

Example:

ID Name Date diagnosis

01 Smith, David 07/01/2004 sore throat

02 Strep Throat

03 Broken arm

For Smith, David he had 3 diagnosis but the field date and name are blank as they would have been the same as ID 01. I have written a query to populate the blank fields:

IIF([name] is null, dlookup("[name]", "tblDiagnosisUnder16final","[Id]=" & [id]-1),[name]) This works great for lines with only one blank, but in the above example for ID 03 the name field stays blank as it is looking at field 02 which is blank in the table.

What I need is a loop to keep moving up the table until it finds a field that <> Null. I assum I should write a function and palce it in the if statement above where the -1 is but I am strugling with comming up with a solution.

Any help would be appreciated.

Mike

View 2 Replies View Related

Vertical Lines

Nov 7, 2005

How would I go about getting vertical lines as column seperators down the entire page. The problem I run into is every time the row size changes there's a break in the column dividers. Someone suggested using a function in the On Print event for the report, but there is no OnPrint field in the event tab of the reports properties. If anyone ever did anything like this before, please let me know.

View 5 Replies View Related

Memo Box New Lines?

Nov 7, 2006

Is there any way of making a comments box (memo) have the functionality to press return and start on a fresh line as it all seems to run into one 'indecipherable blob'

View 1 Replies View Related

Count Of Query Lines

Mar 12, 2007

In a query, how do I do a count of the number of lines that appear in the query? Not the number of records, because the query itself is grouped. The results are in order, highest to lowest, and I want to see just the top 50.

View 3 Replies View Related

Numbering Lines In A Query

Mar 13, 2007

How do I number the lines in a query? I have a 'Top 50' query, and I want to number the lines 1 through 50.

View 6 Replies View Related

Vertical Lines On Forms

Feb 10, 2005

Rather than manually draw vertical lines on my forms, I'm looking for a way to generate vertical lines using VBA code. Has anyone done this?

Thanks,
pc

View 1 Replies View Related

Ruler Lines On Form

Sep 5, 2005

can you get automatic ruler lines to display on a report after each record is displayed?

View 4 Replies View Related

Error Message On 2 Lines

Sep 17, 2006

Hi, I have the following code to display the message as in the picture
Private Sub Staff_Number_NotInList(NewData As String, Response As Integer)
MsgBox "CREW MEMBER NOT IN LIST - Double clic on this field to add a Crew Member"
Response = acDataErrContinue
End Sub
What I wouldlike to do is get the message written on 2 lines instead of 1!!!

Thanks
M

View 1 Replies View Related

Multiple Lines In Text Box

Dec 14, 2004

i'm no noob, but i'm also not the expert, what i'm trying to do is this,
i'm taking multiple string out of an recordset and putting them in one big text box
but the problem is that after every record there has to be an enter in the text box
So for example:

record 1 =
-bla bla bla
-tja tja tja

record 2 =
-waa waa

my result in the text box must be:
-bla bla bla
-tja tja tja
-waa waa

and not (like it is now):
-bla bla bla
-tja tja tja -waa waa

who has a clue? chr(11) & chr(13) do not work, i activated "on enter go to next line"

thx in advance for helping out

View 2 Replies View Related

Import Txt File But NOT The First Three Lines?

Oct 19, 2005

I have a csv file, I am able to import it using the vba commande docmd.TransferText action. But the first three lines are made up of headers. I do not want to import these.

xxxxx,
xxxxx,
Field1, Field2, Fileld3,

Is there a way that I skip out the first 3 lines of the text file? ie only import from row 4 onwards?

Please Help

My current transfer method also creates a table of errors for the first three lines (as they do not match the data type spoecified by the target table). It also creates a whole lot of null fields in the first three rows of the table. I am running a loop for a whole lot of csv files so you can imagine I'm generating a lot of error import table. If I could import only rows 4 onwards there would be peace in the universe!

View 10 Replies View Related

Need To Make My Report Look Like An Excel With Lines In It!

Jun 15, 2006

Does anyone know how to make a report have lines in it like excel can do when you print out. My boss likes to read this data with lines seperating stuff. Thanks.

View 3 Replies View Related

Address Lines And Carriage Returns

Aug 11, 2007

I want my query to output an address in one field with carriage returns, where the address lines in the source table are held in individual fields.

So I try...

SELECT [Address1] & Chr$(13) & [Address2] & Chr$(13) & [Address3] etc..

This does not work.

However, if I use a text box on a form, with "New Line in Field" set, and then in code use Sendkeys to post the address and the carriage returns as above, it does work.

Can this be done simply in a query? If so how?

View 1 Replies View Related

Blank Address Lines In Mailmerge

Dec 27, 2007

I am trying to generate letters from ACC2003 db into Word2003. Reason for Word is so client can edit letters if required.
I have set up a maketable query to generate a temporary table in the Db and the mailmerge master document all formatted and pointed at the temporary table. I still need to get the automation running so that the letters merge and print on a command button.
My immediate issue is that the address lines are intentionally spread across 5 fields Addr1, Addr2, Addr3, Town, Postcode. Not all fields in every record are full, and with the query it would be impossible to eliminate fields on the chance they might be empty.
As a result my mailmerge has blank address lines which do not look professional.
Can anyone point me to how I might solve this given my proposed use - if it was a report then I have a little code module that neatly eliminates blank lines but I cannot see how I could integrate this into the mailmerge process.
Thanks for any help or pointers

View 2 Replies View Related

Count How Many Lines Stored In A Field.

Jun 26, 2007

Is there any way to count how many lines of text are stored in a particular table field? How about counting how many times was the "enter" key pressed?

Thanks

Mafhobb

View 4 Replies View Related

How To Make Separate Lines For Checkboxes

Jun 4, 2007

Hello,

I am using ASP to create a checkbox for 5 values and users can check multiple checkboxes at the same time. I store these values on ms access db table.

If a user check 2 checkbox (e.g. Mexico, Spanish), then the values is stored in the field will be "Mexico, Spanish", separate by "," or comma.

How do I create a query or report that display these values in different line?
Example:
Mexico
Spanish

Or is there any other way to do this?

Thanks for reading.
Jenny.

View 14 Replies View Related

How To Create A Report With Grid Lines?

Aug 22, 2006

Hi,

In my first steps working with Access I feel unable to find out how can I create a report that shows the data (records from a Clients Table - Name, Phone, e-mail fields-) in a "spreadsheet like" format with gridlines. Can anyone help me on this?

Thanks

View 2 Replies View Related

Importing Multiple Lines Of Text

Aug 27, 2007

Good morning. I am new to coding VBA and need some help if possible. I need to import a Comma Delimited Text file into a MS Access table. The records vary in length and may take up multiple lines of text before the next record. The first field contains the type of record and are all prefixed 1###, with the ### being variable. (1001 - 1100). If the record goes over one line the 2nd line with start with 1000. Here is an example: The records with 1001 continue to the next line with 1000 so you know it continues. The other records are all single lines starting with 1100, 1003, 1004, 1006...1017.

1001,00000000,00000000,00000000,00108888,00537906
1000,00100,"CAJUN MEAT ","CHUB "-0020380
1001,00000000,00000000,00000000,00020492,00130534
1000,00108,"RED BEANS ","CHUB ",-0221510
1001,00000000,00000000,00000000,00222572,00796807
1100,0000000000,0009605419
1003,01,000101,263558519,"QQ380427",5,08,19,107,16,07,58
1003,02,000202,263774367,"QQ380428",4,08,19,107,22,22,12
1004,1," "
1004,2," "
1004,3," "
1004,4," "
1006,00000000,00000000,00000000,00000000
1007,"$ OFF ",0000000000,0000000000,0000000000
1007,"FREE ",0000000000,0000000000,0000000000
1007,"SENIOR 10% ",0000000000,0000000000,0000000000
1007,"50% Police ",0000000000,0000000100,0000000001
1007,"CREW 50% ",0000000000,0000000310,0000000003
1007,"CREW 100% ",0000000000,0000001976,0000000009
1007,"MANAGER ",0000000000,0000000260,0000000003
1007,"BONUS ITEMS ",0000000000,0000000000,0000000000
1008,00107,00001,00065,06074,00347,00290
1009,01,"BREAKFAST SALES ",0000114476,02940,000091
1009,02," SALAD SALES ",0000009093,00233,000007
1009,03,"DRIVE-THRU SALES ",0000142716,03666,000106
1010,0000002646,0000009093,0000000000
1011,000,0000000000,003,0000005208
1015,"NET SALES ","+",0000389258
1015,"TAX ","+",0000023434
1015,"GROSS SALES ","=",0000412692
1015,"RESTAURANT BANK ","+",0000000000
1015,"ADJUSTED GROSS CASH ","=",0000412692
1015,"SEPLINE----------------------"," ",0000000000
1015,"AMOUNT DEPOSITED ","+",0000365609
1015,"GIFT REDEEMED ","+",0000000000
1015,"CREDIT CARD ","+",0000044844
1015,"MISC. INCOME ","-",0000000000
1015,"PETTY CASH ","+",0000000000
1015,"NET SALES ","-",0000389258
1015,"TAX ","-",0000023434
1015,"CASH OVER/SHORT ","=",-000002239
1015,"CASHIER OVER/SHORT ","-",-000001839
1015,"OVER/SHORT RECONCILIATION ","=",-000000400
1015,"SEPLINE----------------------"," ",0000000000
1015,"NET SALES ","+",0000389258
1015,"GIFT CERT. SOLD ","-",0000000000
1015,"OTHER NON-FOOD ","-",0000000000
1015,"NET FOOD SALES ","=",0000389258
1016,1,00000000,00000000,00000000
1016,2,00006103,00036415,00118800
1017,0004339338

I will need to do this weekly for many different files. I appreciate all the help that anyone can provide.

View 4 Replies View Related

Queries :: Comparison Of Dates Between Lines

Oct 22, 2013

I am having some "fun" at the moment trying to work out how to compare two times on different rows. The current setup I have is:

StartDTM EndDTM VisitID
09:00:00 10:00:00 1
11:00:00 12:00:00 2
12:15:00 13:00:00 3

etc etc...

I have found a SQL Query I have tried to use, however I am having no joy. The query is such:

WITH rows AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY VisitID) AS rn
FROM Data
)
SELECT DATEDIFF("n", mc.EndDTM, mp.StartDTM)
FROM rows mc
JOIN rows mp
ON mc.rn = mp.rn - 1

View 6 Replies View Related

Queries :: Updating Lines In A Query

Sep 26, 2013

I have created a query which is based on 2 tables.I now want to use the query to update content as needed in one of the tablesWhen I try to do this it says, Operation must use an updateable query..I used the update feature but it still says must be created in updateable query.

View 4 Replies View Related

Queries :: Multiple Lines In A Field?

Jan 8, 2014

I've exported some data which has 3-4 address line within the same field which is very odd.

The row has all the usual data, but when I look at the data in datasheet view the adresss line 1 shows only but when I click into the field and press down, more address info comes up. Is there any way to have this on one row within the field rather than multiple.

A bit like in Excel when you Alt+Tab in a cell it drops down but is the same cell, well this exactly like that in Access 2003.

View 1 Replies View Related