Please suggest relationships model

[Steve]

Do you approach your customers in the same one-track view you have looked at
my response in this thread? You saw I made a response and immediately
convinced yourself to take the Arno R, John Marshall, Keith Wilby narrow
minded view. You denied yourself critical, need to know, information!

Take a look at the OP's original post. Quote: "Can someone suggest an
efficient relationships model to make this work?" Now look at my response.
Here it is in its entirety:

I have assumed that your inventory items are consumable and periodically you
purchase items to replenish your inventory.

TblJob
JobID
JobNumber
JobDescription
<<Job Address fields>>

TblItem
ItemID
ItemDesc
QuantityOnHand

TblItemToJob
ItemToJobID
JobID
ItemToJobDate

TblJobItem
JobItemID
ItemToJobID
ItemID
Quantity
JobItemAssignedCost

TblItemVendor
ItemVendorID
VendorName
<<Vendor contact fields>>

TblVendorItem
VendorItemID
ItemVendorID
ItemID
ItemCost

TblItemPurchase
ItemPurchaseID
ItemVendorID
PurchaseDate
PurchaseOrderNumber

TblItemPurchaseDetail
ItemPurchaseDetailID
ItemPurchaseID
ItemID
Quantity
UnitCost

If you need help, I can help you for a very reasonable fee. Contact me at my
email address below.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Now look at what I provided the OP. He asked for a "suggest an efficient
relationships model" and I gave him my suggestion. It was only after this
that I offered fee-paid help for a very reasonable fee if he needed further
help. The OP had a set of tables to work with just as he asked and if he
felt he could take it from that point he was free to start working on his
database. However, if he wanted his database quick and painless, I provided
him a resource to go to get that. That was additional help provided to the
OP whereby he could avail himself of that help if he so decided. That's
called value added in business jargon. That's the same as telling an OP to
go out and buy Ken Getz's Developer's Handbook if he wants to do something
covered in that book. You frequently see that recommendation in the
newsgroups.

The path to success lies in looking at the forest and not a single tree.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Marshall, MVP]

I have three full time developers working for me. I have done thousands
Gina, this is where the warning bells go off. If he has "helped" thousands,
he would not have time to "help" in the newsgroups let alone solicite
because he would be swamped with follow ups? Is he exagerating about the
numbers or was he able to satisfy them 100% the first time. Some thing seems
fishy even with a client base of 100, he should be busy for life.

John... Visio MVP

 

[John Marshall, MVP]

My fees have always been very reasonable. I strive to work together with

customers and I

Still can not resist the opportunity to solicit?

resource for help with Access, Excel and Word applications.

Your webpages speak volumes about your expertise in these areas.

It has been very successful for me.

So why do you need to annoy posters in these newsgroups with your gorvelling
for work?

After all these years, I am the first to admit I make mistakes.

Pull the other one. You are the last and you usually are in full denial when
your mistakes are pointed out.

However, I always make my best effort to correct my mistakes.

Unfortunately, that is not good enough. Yu are still abusing these
newsgroups.


John... Visio MVP

 

[Gina Whipp]

Steve,

It is obvious you are the one that is on the same track as you STILL don't
see my point. No point in contiuing this as you are going to continue to
abuse these newsgroups. There's an old saying "Trying to change someone is
as useless as trying to teach a pig to dance. It is a waste of time, and it
really pisses the pig off."

I'm done trying...

 

[Gina Whipp]

John,

I must concur... I myself am having a hard time with the 'few' (compared to
thousands) clients I have. I am looking for help because of the support,
upgrades and can you please add issues that come up now! Oh well, I guess I
figured a rational conversation would do the trick but I was wrong.

Off to code I go, as with my skimpy client base I have to work and yet
another weekend!

 

[Steve]

Look again at my post. Quote: "I have done thousands of small jobs for
customers just like the people who post in the newsgroups and a few larger
jobs." There is no mention there of thousands of clients. You saw John
Marshall say that I said I have thousands of clients and you locked yourself
into the Arno R, John Marshall, Keith Wilby mentality.

You have ten years to think about your approach and then let's see where you
are at at that time.



PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

W0W! ... I was not expecting to start a flame war here. Just wanted a little
help with my small little project here. The assumptions tat steve made about
it (the need to replenish the inventory) would be nice, but I dont consider
it necessary at the moment.

Of course, I could hire someone or buy an application to do the job, but I
always wanted to create a useful database, never did take an Access course,
but I can get the hang of most things on my own, with just a tiny little
push. And besides, this is just a one-time deal, so the life of this database
will end when the inventory runs out ( in about 3 - 4 months)

With that said, I might as well add that I pretty much figured out how the
data is stored, and what kind of fields I need to have in my tables.
Relationships is the one thing that I just dont get, which is why I
originally asked for a relationships model.

But thanks (especially Gina for your links), I'll check them out, maybe I
can get what I need to get me going.

 

[John Marshall, MVP]

It was not your fault. Master santos knows the rules and choses to ignore
them.

These newsgroups were set up by Microsoft for free peer to peer support, so
that users can ask questions without being hounded by snake oil salesmen.
Over the past few years, master santos has chosen to be the only one who has
chosen to disregard this fact, pretends to be an expert and hounds
unsuspecting users for work.

John... Visio MVP

 

[Gina Whipp]

No worries, every once in awhile we run off on tagents in here!

Your welcome... I thought one of those links showed data models (which
actually are relationships) they just choose a differet name. Ayway, best
of luck, and come back for more FREE advice any time you want!

 

[Steve]

If replenishing inventory is not necessary, eliminate TblVendor,
TblVendorItem, TblItemPurchase and TblItemPurchaseDetail from the list I
gave you originally.

Regarding reletionships:
1. In TblItemToJob, join JobID from TblJob to JobID
2. In TblJobItem, join ItemToJobID From TblItemToJob to ItemToJobID
3. In TblJobItem, join ItemID From TblItem to ItemID

When you create the relationships, be sure to check referential integrity.

If you need further help, let me know.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

So, after reading a bit, I managed to come up with this model
http://xs317.xs.to/xs317/07311/rel.JPG

That kind of does the trick... I'm just missing one little detail here, and
I'm kind of stuck

I ran the wizard to create a form using the tblTransfers table as source,
came out like this:
http://xs317.xs.to/xs317/07311/fTransfers.JPG

The only problem here is that one of my transactions may include several
items.
How would I accommodate that in this form?

Do I need maybe an additional table to hold more information? if so, what
kind of information would this contain?

Thanks again

 

[Gina Whipp]

Moe,

The image of you relationship model seems slightly different then what you
origially asked for. Where does tblWire fit in the original design? You
can skip past this and get your answer located at the bottom of this
response, I am just adding my 2 cents regarding your table design.

For table design you might want to consider (I may not have taken into
account all the fields you may actually need):

tblInventory
iInventoryID (Primary Key)
iDescription
iQuantityOnHand
iCost
iVendorID (Foreign Key) - Only to be used if Vendors other than yourself

tblProjects
pProjectID (Primary Key)
pDate
pDescription
pAddress1
pAddress2
pCityID
pZipCodeID
pPhoneNumber
pFaxNumber
pMainContact

tblTransactions (This set-up is if you using Tranasactions agaist Projects)
tTransactionsID (Primary Key)
tDate
tInventoryID (Foreign Key)
tProjectID (Foreign Key)
tQuantity (Which is QuantitySold or Purchased)

If there is a Vendor then add this table

tblVendor
vVendorID (Primary Key)
vCompanyName
vAddress1
vAddress2
vCityID
vStateID
vZipCodeID
vPhoneNumber
vFaxNumber
vMainContact
vWebpage

But to answer your question.. You will need a subform that would attach to
the form you already created (One-to-Many) so you can see the transactions
as they relate to the one project.

 

[Guest]

The tblWire is just like the inventory table... I named it like that because
the inventory is mainly wire.
--
Moe

Moe,

The image of you relationship model seems slightly different then what you
origially asked for. Where does tblWire fit in the original design? You
can skip past this and get your answer located at the bottom of this
response, I am just adding my 2 cents regarding your table design.

For table design you might want to consider (I may not have taken into
account all the fields you may actually need):

tblInventory
iInventoryID (Primary Key)
iDescription
iQuantityOnHand
iCost
iVendorID (Foreign Key) - Only to be used if Vendors other than yourself

tblProjects
pProjectID (Primary Key)
pDate
pDescription
pAddress1
pAddress2
pCityID
pZipCodeID
pPhoneNumber
pFaxNumber
pMainContact

tblTransactions (This set-up is if you using Tranasactions agaist Projects)
tTransactionsID (Primary Key)
tDate
tInventoryID (Foreign Key)
tProjectID (Foreign Key)
tQuantity (Which is QuantitySold or Purchased)

If there is a Vendor then add this table

tblVendor
vVendorID (Primary Key)
vCompanyName
vAddress1
vAddress2
vCityID
vStateID
vZipCodeID
vPhoneNumber
vFaxNumber
vMainContact
vWebpage

But to answer your question.. You will need a subform that would attach to
the form you already created (One-to-Many) so you can see the transactions
as they relate to the one project.

 

[Gina Whipp]

Okay then you could make the transfer form you made the subform and the jobs
table the main form. Then you would see the one job to the many transfers.
Is that what you had in mind?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

The tblWire is just like the inventory table... I named it like that
because
the inventory is mainly wire.

 

[Guest]

Yes... that's exactly it...
Thanks for your help
I also need to create a report to display the same information. I'll mess
with that later

I managed to get the form right... allows me to do one transaction, with
many lines in it (one item per line)... That works right...

My main problem at the momment is that mathematical calculations that need
to be done..
For example, in tblInventory, I have a QOH field that shows how many of each
item I have in stock.

In this form, I can pick from a list box the item that I'm shipping out to
the job, and I need to enter a quantity (quantity shipped).

So, I need it to subtract the quantity shipped from the QOH field in
tblInventory.
This may get a little complicated. I'm not sure I know enough make this work.

 

[Gina Whipp]

Moe,

You might want to consider an update query that runs after the transaction
is processed. It would run on the form unload event. I do't thik I would
would ru after the field is updated incase you change the quantity and the
it would be more complicated as you would have retain the QOH each time the
Quantity shipped was changed. The first way I suggested only changes it whe
you exit the form thereby you could chage the Quantity 50 times ad the QOH
is oly updated once.

I would also suggest you start a new thread for that when you are ready,
some folks here might have a better way.

 

[Guest]

I'm afraid queries are a whole different ball game for me...
but, I'll give it a shot. I'll post back if I dont give up on it