Search results

But you could still post the SQL, right? Open the query in Design view. Switch to SQL View and copy and paste the text into a reply.

Andrew- Keep in mind that you can keep the default price for all customers in the Products table. You would only have to enter in the Customer Products Prices table the exceptions for individual customers and products. As I noted earlier, you can use an Outer Join to pick up any relevant...

Andrew- That depends on your business rules. If the special prices are decided individually by customer and product, then you need an entry for every product and customer who gets a special price. If the price is calculated as a discount percentage, then there might be several ways to deal...

If EVERY customer has a unique price that's not the SRP (Suggested Retail Price), then yes. If only some customers get special prices, then you would need rows in that table only for those customer / product combinations. You could set it up with a Left Join to the customer prices table, and...

JK- It's not clear what it is you want to do. I assume your example shows the data as it is now. Can you post an example of what you want the result to be?

Kyle- OK, given a start date and week number, you want to generate the values to the end of the year (week 53), correct? It'll take a bit of code. Dim db As DAO.Database, rst As DAO.Recordset Dim intWeek As Integer, datStartDate As Date, intI As Integer ' Point to this database Set...

Alan- You could take a look at the DateModified property of the TableDef object, but it will probably only show you the date the link was created. You might need to open the back end table directly in code, then look at the TableDef objects there. Even then, the DateModified property is very...

Russ- It would help to see the SQL of your query and perhaps a small sample of the rows you think your predicate should match.

Kash- Try this: UPDATE Customer SET [Client Name] = DLookUp("Client Name","Customer","Account Number= '" & [Account Number] & "' AND Len([Client Name] & '') > 0") WHERE Len([Client Name] & "") = 0; It might be the case that some of your Client Name field contain a zero length string rather...

GSan- You would have to save the query as a QueryDef, then define and set the ColumnOrder property of that totals field to place it at the end of the display. In this case, it looks like you have one Row Heading column followed by the six application names. You would need to set ColumnOrder...

Andrew- You're having problems because your structure is all wrong. The tables should look like this: tblCustomers: CustomerID, CustName, CustAddress, etc. tblProducts: ProductID, ProdName, ProdVendor, ProdDescription, ProdSRP, etc. tblCustProdPrices: CustomerID, ProductID, CustProdPrice...

You can use text boxes in the form footer of a Continuous form to calculate using any of the Aggregate functions (Sum, Min, Max, Avg, etc.). For example, if you want the sum of the calculated item cost, do: =Sum([Qty] * [Price]) That will do the calculation across all rows displayed in the...

Andrew- Please explain what you mean by "master table." You should NEVER store calculated values in a table, much less use a repeating group such as suggested by your Cust 1, Cust 2, etc. columns. If the discount rate is in the Customers table (or is it by product or product class?), then it...

All you need to do is repeat the expression you're using to "calculate" values in your query trying to find an average. For example, if the amount of the purchase is Qty * UnitPrice, then you simply need Sum(Qty * UnitPrice) to total the order. It would help to know the structure of your...

Dear Ions- It's tough to see what might be causing a full table lockout without seeing your code. You would normally wrap a transaction around updates to rows in multiple tables to ensure they either all commit or none commit. Such "batches" of updates should be well-defined and compact. In...

That's not possible unless you match on other criteria. For any given combination of Main, From, To, and SOS, you have duplicate rows, so linking on just that criteria (whether you use the four fields or the unique ID we generated) will yield a Cartesian product - apparently 11,000+ rows! If...

Close, but no cigar. You have to save the first UNION query, then use it as the source in your Append query. Let's say you save the first query as qryAllCodes. Your insert looks like: INSERT INTO DRIVER (SGNH_Hra_Main_Street, SGNH_Hra_From_Street, SGNH_Hra_To_Street, SGNH_Hrap_Sos) SELECT *...

Do a JOIN on the matching ID values that you have generated, but be aware that this will get you the Cartesian product of all rows with matching IDs and will not be updatable. It will let you see any minor differences in the rows that all have the same combination of Main, From, To, and SOS.

You still need a "driver" table, but you can reload it each time you need to compare two tables. First, create the "driver" table with these fields: ID - AutoNumber Main From To SOS Define a Unique key on Main, From, To, and SOS Now each time you need to compare two tables, do this: 1)...

Yup. As long as you can create a "master" table with all the possible Main / From / To / Side / Sign combinations with a unique ID, you should be able to use that as a "driver" table to assign the ID to rows in other tables and then compare them.