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 » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

design access



 
 
Thread Tools Display Modes
  #1  
Old March 9th, 2010, 11:21 AM posted to microsoft.public.access.tablesdbdesign
Revned
external usenet poster
 
Posts: 48
Default design access

Hello everyone,

I have a table tblOrders
CustomerName DateOrder RefNo Amount Status

primary key: RefNo
Field type: Status - check box

I have a table tblPayments
CustName Datepaid PaidRef AmntPaid

primary key: PaidRef

can anyone help me figure out how can I design my query and my form/subform
that when in my tblPayment field name AmntPaid equals to tblOrder field name
Amount, access will automaitcally set the field name Status to Yes.

please can anyone help me.

thanks in advance, i appreciate
  #2  
Old March 9th, 2010, 12:45 PM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default design access

hi,

On 09.03.2010 11:21, Revned wrote:
can anyone help me figure out how can I design my query and my form/subform
that when in my tblPayment field name AmntPaid equals to tblOrder field name
Amount, access will automaitcally set the field name Status to Yes.

This makes not really sense. How do you know which payment matches which
order?

btw, your tables are not well designed.

I have a table tblOrders
CustomerName DateOrder RefNo Amount Status
I have a table tblPayments
CustName Datepaid PaidRef AmntPaid

There should be a table Customer which should referenced in both tables
instead of storing the customer name reduuntantly.


mfG
-- stefan --
  #3  
Old March 9th, 2010, 02:47 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 1,555
Default design access

?The way you are tying a payment amount to an order is by the Amount?

Is there ever a situation when two orders have the same Amount?

Do you ever have a situation where someone pays less than (?!more than) the
full Order Amount?

I'll suggest that you take another look at the Northwind database that comes
with Access for a possible model of the data structure to use for an Order
application.

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.



"Revned" wrote in message
...
Hello everyone,

I have a table tblOrders
CustomerName DateOrder RefNo Amount Status

primary key: RefNo
Field type: Status - check box

I have a table tblPayments
CustName Datepaid PaidRef AmntPaid

primary key: PaidRef

can anyone help me figure out how can I design my query and my
form/subform
that when in my tblPayment field name AmntPaid equals to tblOrder field
name
Amount, access will automaitcally set the field name Status to Yes.

please can anyone help me.

thanks in advance, i appreciate



  #4  
Old March 9th, 2010, 03:48 PM posted to microsoft.public.access.tablesdbdesign
Revned
external usenet poster
 
Posts: 48
Default design access

This makes not really sense. How do you know which payment matches which
order?
through field name RefNo, it would match the payments.
Thus it make sense??
please can you help me, I already try to remove the CustomerName to avoid
redundancy.

thank Stefan Hoffman, I really appreciate your reply

"Stefan Hoffmann" wrote:

hi,

On 09.03.2010 11:21, Revned wrote:
can anyone help me figure out how can I design my query and my form/subform
that when in my tblPayment field name AmntPaid equals to tblOrder field name
Amount, access will automaitcally set the field name Status to Yes.

This makes not really sense. How do you know which payment matches which
order?

btw, your tables are not well designed.

I have a table tblOrders
CustomerName DateOrder RefNo Amount Status
I have a table tblPayments
CustName Datepaid PaidRef AmntPaid

There should be a table Customer which should referenced in both tables
instead of storing the customer name reduuntantly.


mfG
-- stefan --
.

  #5  
Old March 9th, 2010, 04:10 PM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default design access

hi,

On 09.03.2010 15:48, Revned wrote:
This makes not really sense. How do you know which payment matches which

order?
through field name RefNo, it would match the payments.
Thus it make sense??
please can you help me, I already try to remove the CustomerName to avoid
redundancy.

Okay, I see. This will still result in a problematic case:

I have a table tblOrders
CustomerName DateOrder RefNo Amount Status
primary key: RefNo
Field type: Status - check box

I have a table tblPayments
CustName Datepaid PaidRef AmntPaid
primary key: PaidRef

So your relation is

tblOrders(RefNo) - tblPayments(PaidRef)

If PaidRef is really your primary key, this means you can only have on
payment per order.

Normally you would use a table structure like this:

Order:
ID, AutoNumber, Primary Key, Not Null
idCustomer, Number(Long), Not Null
DateOrder, Date/Time, Not Null,
Amount, Currency, Not Null
RefNo, Text(255)

Payment:
ID, AutoNumber, Primary Key, Not Null
idOrder, Number(Long), Not Null
DatePaid, Date/Time, Not Null
Amount, Currency, Not Null

with a relationship with relational integrity from

Order(ID) 1 - n Payment(idOrder)

Then you would use a query like:

SELECT
O.*,
(O.Amount =
(SELECT SUM(P.Amount)
FROM Payment P WHERE P.idOrder = O.ID)) AS IsPaid
FROM [Order] O

Or as an UPDATE query:

UPDATE [Order] O
SET O.Status = True
WHERE O.Amount =
(SELECT SUM(P.Amount)
FROM Payment P WHERE P.idOrder = O.ID)


mfG
-- stefan --
 




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 07:52 PM.


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