A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Please suggest relationships model



 
 
Thread Tools Display Modes
  #31  
Old July 30th, 2007, 07:30 PM posted to microsoft.public.access.queries
Moe[_2_]
external usenet poster
 
Posts: 7
Default Please suggest relationships model

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
--
Moe

  #32  
Old July 30th, 2007, 10:02 PM posted to microsoft.public.access.queries
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Please suggest relationships model

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

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

"Moe" wrote in message
...
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
--
Moe



  #33  
Old July 30th, 2007, 10:38 PM posted to microsoft.public.access.queries
Moe[_2_]
external usenet poster
 
Posts: 7
Default Please suggest relationships model

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


"Gina Whipp" wrote:

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

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

"Moe" wrote in message
...
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
--
Moe




  #34  
Old July 31st, 2007, 01:12 AM posted to microsoft.public.access.queries
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Please suggest relationships model

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
"Moe" wrote in message
...
The tblWire is just like the inventory table... I named it like that
because
the inventory is mainly wire.
--
Moe


"Gina Whipp" wrote:

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

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

"Moe" wrote in message
...
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
--
Moe






  #35  
Old July 31st, 2007, 06:12 PM posted to microsoft.public.access.queries
Moe[_2_]
external usenet poster
 
Posts: 7
Default Please suggest relationships model

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.
--
Moe


"Gina Whipp" wrote:

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
"Moe" wrote in message
...
The tblWire is just like the inventory table... I named it like that
because
the inventory is mainly wire.
--
Moe


"Gina Whipp" wrote:

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

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

"Moe" wrote in message
...
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
--
Moe







  #36  
Old July 31st, 2007, 06:26 PM posted to microsoft.public.access.queries
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Please suggest relationships model

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.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"Moe" wrote in message
...
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.
--
Moe


"Gina Whipp" wrote:

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
"Moe" wrote in message
...
The tblWire is just like the inventory table... I named it like that
because
the inventory is mainly wire.
--
Moe


"Gina Whipp" wrote:

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

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

"Moe" wrote in message
...
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
--
Moe









  #37  
Old July 31st, 2007, 07:10 PM posted to microsoft.public.access.queries
Moe[_2_]
external usenet poster
 
Posts: 7
Default Please suggest relationships model

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
--
Moe


"Gina Whipp" wrote:

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.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"Moe" wrote in message
...
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.
--
Moe


"Gina Whipp" wrote:

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
"Moe" wrote in message
...
The tblWire is just like the inventory table... I named it like that
because
the inventory is mainly wire.
--
Moe


"Gina Whipp" wrote:

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

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

"Moe" wrote in message
...
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
--
Moe










 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.