Search results

The way I set up a method to pull out the unique groups, assign a number, then put that number back in the original table will not guarantee that the numbers will match what you have in any "old" table. I don't see how assigning the numbers will help you "match" to an old table unless you take...

A FULL OUTER JOIN is a bit tough to do in Access. You have to UNION a LEFT join with a RIGHT join. Can you explain in more detail what it is you're trying to achieve?

OK, here's what I did: 1) I performed the steps 1-4 listed before. (I named the table TestReverseUnique this time.) 2) I copied TestReverseUnique to the clipboard and pasted it Structure Only as TestReverseUniqueSeq. 3) I opened TestReverseUniqueSeq in Design view, removed the Primary Key...

Ah, then that begs the question: Why do you want to keep the duplicate rows?

To get unique rows, you can do this: 1) Make a copy of your table without the ID field - copy to the clipboard, paste structure only, and then delete the ID field. (See table TestReverse in the attached sample.) 2) In the new table, make the combination of the fields 1_SGNH_Hra_Main_Street_X...

A Primary Key, by definition, has a unique value in each row. If you choose a field that does not have unique values, you will get an error when you try to save the table definition. If you have more than one field that has unique values, then no, I suppose it doesn't matter. John Viescas...

It's possible to do if there's a Primary Key defined on the table. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France)

Why are you printing a form? That's not normally a good idea. Better to design a report. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris...

Yes, as long as the printer is defined on the machine. John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France)

Sure. Change qryTotals to this: SELECT qryDetails.prod_type, Sum(qryDetails.produced) As TotalProduced, Sum(qryDetails.sheets) As TotalSheets, Sum(qryDetails.sales_qty) As TotalSales_Qty, Sum(qryDetails.sheets_sold) As TotalSheets_Sold, Sum(qryDetails.sales_val) As TotalSales_Val, Sum(Cost)...

OK, just for review. Your query that should be named qryDetails: SELECT [1-1 history week].dated AS movement_date, [1-3 sales week].dated AS sales_date, [1-0 products].dept, [Manufacturing Department].[Department Name], [Sub Group].prod_type, [1-0 products].product, [1-0...

Note that my Totals query as written will sort on long_description, then sub_grp, not prod_type. I think I would need to replace sub_grp with prod_type to get something to work with. Is that really how you want it grouped? It should match your sort sequence. Can you give me the data type of...

OK, let's call the query below "qryDetails". A "totals" query to get the values you want would look like: SELECT qryDetails.long_description, qryDetails.sub_grp, Sum(qryDetails.produced) As TotalProduced, Sum(qryDetails.sheets) As TotalSheets, Sum(qryDetails.sales_qty) As TotalSales_Qty...

Instead of pulling the data from Access using Excel, you could push the data from Access and use automation to insert rows and the total calculations. Another idea is to create a second query to calculate the totals, use a UNION of the original query and the totals, and pull the UNION query into...

Great! I have no clue why it was insisting on displaying integers.

Are any of the calculated fields showing with decimals? You could try using 1.0 instead of 1 to help preserve the floating point, but if any of the values in each expression is floating point, then the query engine should be using that. The only other thing that might help is to slap CDbl...

What is the SQL of the two queries? It sounds like you have a format defined in the second one or have used an expression that converts the result to Integer.

There does seem to be some interesting interaction between DataEntry and FilterOn. I played with it a bit and discovered: 1) Opening a form in DataEntry with a filter does put you in Data Entry mode, but the filter is NOT honored. (You can add a record that doesn't match the filter, and it...

Actually, Dirty doesn't get set, so telling the user to press Esc is unnecessary. After disabling the Form_Current event in Northwind 2007 Order Details, this works just fine: Private Sub Form_BeforeInsert(Cancel As Integer) If IsNull(Me.Parent![Order ID]) Then MsgBox "You must...

All the time in subforms. Private Sub Form_BeforeInsert(Cancel As Integer) If IsNull(Me.Parent.Pkey) Then MsgBox "You must enter data in X (the parent table) " & _ before creating a Y (the subform table). Press Esc " & _ "to clear your edit.", vbInformation Cancel = True...