Hello I am trying to create a

[JSD]

Hello

I am trying to create a qry using tbls from an MRP system. The tbls are linked to a network and cannot be modified for security reasons.
The qry is pulling a bunch of part numbers that are past due. Each past due part number has a related so_no and a seqnr. This works fine.
I want to be able to run this qry and type in a due date value onto my own tbl. My tbl, along with a due date field, has both the so_no and seqnr as primary keys. I tried relating so_no to so_no and seqnr to seqnr on the MRP tbl, but all I get are intermmediate joins, and the qry doesn't return anything. I can change a join in the qry to Left Join, and the due date field shows up but it wont let me type anything in it.
Any suggestions on how to add a due date to this qry? A subquery, maybe? (I loooked into it, but didn't find much help there). Any suggestions would be helpful and appreciated.

Thanks

Jeremy

P.S. - Here is the working qry without a due date field.

SELECT dbo_bcklines.part_no, dbo_bcklines.seqnr, dbo_bcklines.partdesc, dbo_hnymastr.part_class, dbo_hnyquan.on_hand, dbo_bcklines.pre_invoiced, dbo_bcklines.so_no, dbo_bckmastr.order_date, dbo_bcklines.ship_date, dbo_customer.sort_name, dbo_bckmastr.credit_hold_flag, dbo_bcklines.open_qty, dbo_hnyquan.store_no
FROM (dbo_hnymastr INNER JOIN dbo_hnyquan ON dbo_hnymastr.part_number = dbo_hnyquan.part_no) INNER JOIN ((dbo_customer INNER JOIN dbo_bckmastr ON dbo_customer.cuscode = dbo_bckmastr.cuscode) INNER JOIN dbo_bcklines ON dbo_bckmastr.so_no = dbo_bcklines.so_no) ON dbo_hnymastr.part_number = dbo_bcklines.part_no
WHERE (((dbo_hnyquan.on_hand)<([open_qty]-[pre_invoiced])) AND ((dbo_bcklines.ship_date)>=[Start Date] And (dbo_bcklines.ship_date)<=[End Date]) AND ((dbo_bckmastr.credit_hold_flag)<>&quot;H&quot AND ((dbo_bcklines.open_qty)<>0) AND ((dbo_hnyquan.store_no)=&quot;100&quot);

 

[scriverb]

What you are using is a SELECT query which can only SELECT records and data and pass a recordset to a form, report, VBA code, etc. If you want to UPDATE an exisiting table that has the same structure you use an UPDATE query to do this. You use much the same process of joining the tables but you identify the fields in the selected tables that you want to update to fields in your target table for updating. If you want to APPEND NEW records to an existing table you can use an APPEND query to select records using the same technique as your SELECT but they are then use to add NEW records to a table. Lastly if you want to create a new table you use a Make-Table query to create this new table. The query will perform the SELECT as before but create a new table and append the records to it. If there is an existing table with the same name it will overwrite it.

Now you have to determine what process fits you the best. In the QDW there is a button and a menu selection to select the type of query that you want to design. The button is just to the left of the red exclamation point in the center of the toolbar. The query type selections are under the Query menu in the menu bar.

Let me know if you need any help setting this up. Bob Scriver

 

[JSD]

Hello again

I was thinking about using update query, but when I add the due date field from my tbl and put blank brackets in the update to cell, the query asks for a parameter. I think whatever value I type in that cell is going to show on all the records the query. I need to be able to type a different date for every record pulled in from the original working query. I don't want to append or make-table because I need to be able to run the query at least daily and type in due dates, keeping due dates from the day before until a late order in the query ships and is not in the query anymore. Need help setting this up...

Thanks

Jeremy

 

[danvlas]

If I'm not mistaken, a read-only table added to a query renders the query non-updatable. So you cannot use the master table in the definition of From part.

You could use a domain aggregate function:

Update CopyTable Set WhateverCopyField = DLookup(&quot;WhateverMasterTable&quot;, &quot;MasterTable&quot;, &quot;so_no =&quot; & CopyTable![so_no] & &quot; And [seqnr] = &quot; & CopyTable!seqnr)


Just an idea...

Good luck


Daniel Vlas
Systems Consultant

 

[JSD]

Hello again

Thanks for the idea. I'm trying to figure out where to fit this into the original SQL statement. Could I ask for some direction on this one...

Thanks

Jeremy