I appreciate it -
There isn't actually a 1:1 relationship... I just described the scenario simplistically trying to keep the thread simple and the issue spotlighted.
We're actually a contract manufacturer serving many customers - you could substitute "customer part number" for "internal part number", and the inventory table reflects what actual, real, physical parts we actually have in inventory...
geolemon
Last Activity: Feb 26 '09, 11:35 PM
Joined: Aug 28 '08
Location:
-
Primary key is MfgPN in the parts table, since it has a many-to-one relationship to CustPN. In that table, CustPN is just an attribute associated with MfgPN because of that relationship.
I think what I'm going to do is create two stored queries...
--- One to identify the min Qty and min MfgPN (since I at least need it returned but need the 'group' by to work) where Qty>0
---The second to identify the same thing, where...Leave a comment:
-
Hopefully SQL can handle processing alternative part numbers?
I'm building a small-scale MRP system for an electronics manufacturing business. There are assemblies that are built, and parts that go into those assemblies. The individual component parts that go into each assembly have their own component part numbers, and since many manufacturers make those basic components, often times alternative part numbers (and quantities) exist in the inventory system for a given component part.
Fortunately,... -
Form problem - LinkMasterFields error (only when adding a new record)?
I have a form/subform used for inventory management.
It is a bound form.
The error:
I don't understand what this means.
How the form is intended to work:
When you select a part number in the main form, the subform displays potentially several instances of that part number in inventory, and is designed to allow the inventory manager to adjust the quantities for each instance of the part in... -
Installing Access 2003 on Office 2007 workstations? Thoughts on?
I developed an Access database on my laptop, which has Access 2003.
The workstations in our office have Office 2007 installations, which did not include Access (Student and Teacher edition possibly?).
Since we all "grew up" with Office 2003 and earlier, even quick adapters feel a bit like lost puppies, full of "Where did they put THIS function???" in Office 2007. The (cough) terms of endearment for... -
Fish -
I'd have to do it row-by-row, stepping through the table, more VBA than SQL...
One description might have 2 words, another 12, with unpredictable amount of space in between. It's an option, just a bit more complicated than I had hoped for - I've really got to write an entire program around that.
Stewart -
And just as I say the above, you seem to have done all the work for me, lol.
Nice!...Leave a comment:
-
Please tell me something like THIS isn't the solution:
REPLACE(REPLACE (REPLACE(REPLAC E(REPLACE(REPLA CE(RE PLACE(DBimport. description,"----", "-"),"---","-"),"--","-"),"--","-"),"--","-"),"--","-"),"--","-")
I might have gone a little overboard - but that's part of what I'm trying...Leave a comment:
-
How to trim misc blanks within strings?
Any way to do this as part of a data cleansing routine?
One customer has supplied text that looks like this:
" 100 UF_____________ _______16V_____ ________6555___ __________ELEC_ ___________SM__ __"
Another row:
"___0.1UF______ ________16V____ ________0603___ __________X7R__ _______________ _____"
Obviously, it would be much more desirable to store that as:
"100 UF... -
The field is [NOT] too small to accept the amount of data you...
I'm getting an error, I'm not seeing the cause:
Debug highlights this segment of code (I just snipped a portion here):
...Code:strProc = "Insert into Parts (MfgPN, Manufacturer, Description)" & _ "select distinct PurchasedPN, Manufacturer, Description " & _ "from DBimport where PurchasedPN is not null " & _ "and -
@#$@% report! Data order issue... simple data order issue.
I'm having a frustrating issue with a report that WON'T order my data properly, seemingly whatever I do.
Surely I must be overlooking something!
I have an "order by" in my raw query:
...Code:SELECT DISTINCT ViewInventoryStatus.CustPN, ViewInventoryStatus.MfgPN, ViewInventoryStatus.Qty, ViewInventoryStatus.Manufacturer, ViewInventoryStatus.Description, ViewInventoryStatus.PartsOwner,
-
I had a thought that I could create a query with a WHERE clause that references something that doesn't exist, like "WHERE AssyPN = [UserInput]".
Then, I could build a report off that query...
And build a message-box type form with a control called "UserInput" asking the user to input the assembly he wants to limit the report to, and launching the report when the "OK" button is pressed.
...Leave a comment:
-
Form or report?
I'm still relatively new to Access - this question is due to my moderate experience with forms and my complete and total lack of experience with reports.
I want to create a report (I use that term conceptually) that prompts an end user to input an assembly number, and kicks out a report that sums up what and how many of each component is used in an assembly.
So, my first question is - how do I create a report that prompts... -
Oh, sorry -
by "right", I meant "correct".. .
I was struggling with the corrrect join syntax, because I'm used to simply doing something like this:
I'm amazed that Access apparently wants some sort of "join order" rather than letting the database query...Code:SELECT * FROM TABLE1 inner join TABLE2 on KEY1=KEY2 inner join TABLE3 on KEY1=KEY3 inner join TABLE4 on KEY3=KEY4
Leave a comment:
-
Do you mean by simply joining and letting the duplication do the work?
I suppose I can test that easily enough using a Select query to ensure that I'm not inadvertantly causing multiplication or other cartesian-product-like ill effects.
I definitely am a fan of the explicit rather than the implicit by rule... but everyone has to step out of their comfort zone sometime, right?...Leave a comment:
-
Not "by necessity"...
I can do this using views in other DBMS's ;-)
I didn't think I created a non-updatable query, as I'm not updating the query - I"m updating the table directly, and attempting to join the query with the group-by merely adjacently, to pull my new value from - as I'd do outside the world of Access.
I was more suspicious of a syntax error, as I've been fighting with this "query...Leave a comment:
-
I'm not sure if it'll help since this is just ONE way I've tried it.
Actual table names used here - not hard to figure out:
...Code:UPDATE Inventory INNER JOIN (Parts INNER JOIN ((AssemblyParts INNER JOIN JobOrders ON (AssemblyParts.AssyPN = JobOrders.AssyPN) AND (AssemblyParts.Rev = JobOrders.Rev)) INNER JOIN AssemblyPartsCounts ON (AssemblyParts.AssyPN = AssemblyPartsCounts.AssyPN) AND (AssemblyParts.Rev = AssemblyPartsCounts.Rev)
Leave a comment:
-
Update query: "Operation must use an updatable query" error
I'm trying to perform an update, and I can't avoid this error - I've tried this query what must be 5 different very fundamentally different ways now.
Arg.
I used to be a DBA in large DB2 and SQL Server environments, but I'm a little rusty, plus I'm very new to Access.
I am trying to perform a calculation based on information in a few tables, and use the result to update another.
Should be simple,... -
So possibly there was a space or other non-visible character an those columns, somewhere?
Thinking "I need to make this a repeatable process", then you'd speculate with me that either of these precautions would work, right:
Either one:- Highlight "a good number" of columns to the right of "Descriptio n" and press "Delete, and highlight a "good number" of rows below my last
Leave a comment:
-
Eureka!!!!
I decided to just let it fly - import it into a NEW table, and I found something:
It was trying to import two extra columns (to the right of the ones I listed), and 30 or 40 extra rows of data (all blank cells, I presume from below the rows that were actually populated - although when I opened the table they appeared at the top.
I deleted the extra rows and columns, and was able to insert without...Leave a comment:
-
is the error message I originally got directly importing this one particular Excel file, followed by
The header row is as follows:
RefID
DNP
CustPN
BOMPN
PurchasedPN
PNSource
Manufacturer
Description...Leave a comment:
No activity results to display
Leave a comment: