INSERT query wrong file path

[Lol999]

The penny has just dropped with a huge clunk! Part of what I am trying to do was never going to happen the way I was doing it, don't know what happened to my brain but there you go

I have re-made the original database from scratch.

I will have some new questions I suspect but I will try to be ultra explicit on what I am trying to achieve to make life easier for everyone else!

Thanks, Lol

 

[isladogs]

The penny has just dropped with a huge clunk! Part of what I am trying to do was never going to happen the way I was doing it, don't know what happened to my brain but there you go

I have re-made the original database from scratch.

I will have some new questions I suspect but I will try to be ultra explicit on what I am trying to achieve to make life easier for everyone else!

Thanks, Lol

I know I said I would drop out of this thread but just want to say - hooray to that!

 

[jdraw]

??? PAT testing??? This is part of what I call jargon-ese.

Glad you are taking a fresh start.
The links on this page may be useful.

I like to start from a clear description and build and test a model as per these tutorials.
http://www.rogersaccesslibrary.com/Tutorials/ClassInformation.zip45
http://www.rogersaccesslibrary.com/Tutorials/ConsolidatedWidgetsDesign.zip

You have to work through them ~45 minutes each, but you will learn a procedure you can use with any data base.

Good luck.

 

[Gasman]

jdraw,

PAT is the acronym for Portable Appliance Testing
Over here Health and Safety require employers to to provide a safe working environment for their employers and that involves all electrical items being tested for being in a good state of repair, electriclally and physically.

This generally means you are not allowed to take your own equipment into work (like a fan) as it will not have been tested. Carried out every one or two years.

https://en.wikipedia.org/wiki/Portable_appliance_testing

I would hazard a guess the USA has something similar?

??? PAT testing??? This is part of what I call jargon-ese.

.

 

[The_Doc_Man]

I've kept out of this because other help was available, but I have an answer for one of the questions. In post #12, that diagram shows two tables with a one-to-one relationship AND the form of the connector suggests that relational integrity is turned on. In essence, these two tables are in a deadlock (about as bad as Harry Potter and Voldemort) that neither can be updated until the other is updated and you can't update them simultaneously. I'm not even certain that you could do the update with a JOIN query that included both of the one/one keys at the same time.

 

[jdraw]

Lol999,

Did you look at the database I mentioned in earlier post?

Do you have some details re your processes in plain English?
Definitions/examples of tools, locations, Jobs, Stores?
Readers will help, but we need something to work with.

Good luck.

 

[Lol999]

I'm going to watch the videos to refresh the brain, but in the mean time can anyone explain what is wrong with the relationship attached?
A tool can be booked to many locations but only one at a time, and a location can have many tools booked to it.

Thanks, Lol

 

[jdraw]

A ToolBooking would have Start and End Date/Time.

eg: I try to "Book" a BobCat part no 25 on Jun 23/17, but it is already "booked by someone else". So a "Booking" can only occur if the specific Tool is available.
Somehow you will need to have a process to determine tools available.

If you "play stump the model" by arranging your proposed tables and attributes on paper, and add some sample test data, you can pose various questions/scenarios against the model, and determine if you can get the proper data to satisfy the scenario.
If you can't get the "right info", is it the model, the attributes, the scenario etc???
You then have to adjust to meet your requirements.

Listing a few sample conditions may help with your analysis.

 

[Lol999]

Ah, therein lies part of the problem. Tools cannot be pre-booked, this is simply a method to know where they are. So, they are either in stores, or they are on site.
The purpose of the db is to locate tools when they are due for recall for test.
The tools themselves are such things as extension cables, drills, angle grinders, all stocked in multiple quantities so the need to pre-book doesn't exist.

With that caveat is the data structure sound, and if not, can someone please explain why because this is how I was taught at college?

 

[jdraw]

Tools cannot be pre-booked, this is simply a method to know where they are. So, they are either in stores, or they are on site

. Or they were lost or stolen or removed from service

The purpose of the db is to locate tools when they are due for recall for test

. Where do you record Date forTest?

The tools themselves are such things as extension cables, drills, angle grinders, all stocked in multiple quantities so the need to pre-book doesn't exist.

If you start with 50 of each, how many exist after 3 months? 1 year?
How do you know? Which ones were lost?
Which were stolen? Which were damaged and removed from Service?
How do you know what has to be PAT tested?

Can you describe a typical day in your business?

ABC needs a Part X on Job 9, what gets done and recorded, exactly?
What if there are no Part X available? What is/are the options?
Someone returns a damaged Part--what happens?
You need to do a PAT Test on Part21 -How do you know where it exists? Who gets called? How does part21 get ready for the PAT test?
Is there anyone assigned to the tool or responsible for the tool?

Can you tell us more about Stores? How many etc.

These are the kinds of things you need to analyze to determine what tables, attributes and relationships exist.

Good luck.

 

[Lol999]

Believe me when I tell you that all those points you raised have been considered and the data structure remains the same.
I might need my skills on db updating but systems and methodologies is something I'm quite au fait with.
This is not some hastily put together solution, many hours have been spent in the background analysing working practices in existence, existing technologies in use and possible upgrades, the aims of the company and its existing culture and its aspirational one along with the likelihood of acceptance and successful adoption by the workforce.
Effective is not always complicated.

BTW, the solution to my problem lies in List boxes and recordsets created in vba. I have done this before in the past, just need to dust off the memory and ask a few pertinent questions when the time comes

Cheers, Lol