Can't figure out the right Table Design

[sara]

I have been working this idea for a little over a week now and am not
convinced I have a good solution.

I have a little system that tracks Purchase Orders (POKey autonum,
PONum are both ont tblPOData, along with all the detail fields).

Recently the users asked if we could track Billing Information. "Of
course", I said.

Here's the thing:
We get an invoice that may be Freight for multiple POs, all on the same
invoice.

I need to have the users enter JUST the freight charge for the
individual PO, and attach it to that PO, so I can add up all the Actual
Freight charges to have TOTAL Actual Freight.

The users call the One Freight Bill, Multiple POs - Freigh FAK (Freight
of all Kinds).

One PO CAN also have freight on multiple FAK Invoices.

I have a button on the PO Update form that says "Freight". They press
the button and they can then enter all the freight for that PO. Works
fine.

They want, however, to "Identify" if the freight amount they're
entering is part of an FAK invoice (maybe I should use Invoice Type?)

They also want to enter the FAK Invoice Number, Freight Company (I'd
store the key), InvoiceDate and InvliceTotalAmt, so I can run reports
and say "Invoice 402 was for $1005, but you entered total freight on
POs for invoice 402 of $1010. Check your entries and correct" - or
whatever.

Here's what I have. I can't seem to figure out how to get the data and
relationships right, nor do I know how to have them enter the FAK info.
I thought if they choose "FAK" as invlice Type, show a popup that
would require them to enter the FAK invoice info? I'm lost.

tblFreightBill
freightBillKey - autonum
POKey - num
InvoiceNum - num
FreightCokey - num
BillingDate - date
FAKKey - number (I was thinking to use this to link to the other table?
Examine it - if 0, not an FAK?

tblFAK
FAKKey - autonum
InvoiceNum - num (would have to be the same as on the PO screen)
FreighCoKey - num (would have to be the same as on the PO screen for
the FAK portion - the PO can also have freight charged from another
company as well)
InvoiceAmt - currency
InvoiceDate - date (would have to be the same as on the PO screen)
Thanks - I appreciate any help. This is much more sophisticated than
anything I've done in my 6 months or so writing Access apps.

Sara

[ManningFan]

You're going to need to track P.O. # and Invoice #. You can then find
multiple instances of either P.O. # or Invoice # by writing a Find
Duplicates query (use the wizard, it's easier that way).

Then do a dlookup on the DISTINCT numbers your query finds. This
should get you all the P.O. and Invoice numbers that have multiple
records associated with them.

Not an easy project for a n00b, you may want to write a process map and
just do one step at a time.
sara wrote:

I have been working this idea for a little over a week now and am not
convinced I have a good solution.

I have a little system that tracks Purchase Orders (POKey autonum,
PONum are both ont tblPOData, along with all the detail fields).

Recently the users asked if we could track Billing Information. "Of
course", I said.

Here's the thing:
We get an invoice that may be Freight for multiple POs, all on the same
invoice.

I need to have the users enter JUST the freight charge for the
individual PO, and attach it to that PO, so I can add up all the Actual
Freight charges to have TOTAL Actual Freight.

The users call the One Freight Bill, Multiple POs - Freigh FAK (Freight
of all Kinds).

One PO CAN also have freight on multiple FAK Invoices.

I have a button on the PO Update form that says "Freight". They press
the button and they can then enter all the freight for that PO. Works
fine.

They want, however, to "Identify" if the freight amount they're
entering is part of an FAK invoice (maybe I should use Invoice Type?)

They also want to enter the FAK Invoice Number, Freight Company (I'd
store the key), InvoiceDate and InvliceTotalAmt, so I can run reports
and say "Invoice 402 was for $1005, but you entered total freight on
POs for invoice 402 of $1010. Check your entries and correct" - or
whatever.

Here's what I have. I can't seem to figure out how to get the data and
relationships right, nor do I know how to have them enter the FAK info.
I thought if they choose "FAK" as invlice Type, show a popup that
would require them to enter the FAK invoice info? I'm lost.

tblFreightBill
freightBillKey - autonum
POKey - num
InvoiceNum - num
FreightCokey - num
BillingDate - date
FAKKey - number (I was thinking to use this to link to the other table?
Examine it - if 0, not an FAK?

tblFAK
FAKKey - autonum
InvoiceNum - num (would have to be the same as on the PO screen)
FreighCoKey - num (would have to be the same as on the PO screen for
the FAK portion - the PO can also have freight charged from another
company as well)
InvoiceAmt - currency
InvoiceDate - date (would have to be the same as on the PO screen)
Thanks - I appreciate any help. This is much more sophisticated than
anything I've done in my 6 months or so writing Access apps.

Sara

[sara]

Thank you very much.

I'll start working on your suggestions right away. Please be patient;
this may take me a few days to get it. I'll post back with
questions/problems, for sure. BUt what you say makes sense, so maybe I
can do it?

Sara

ManningFan wrote:

You're going to need to track P.O. # and Invoice #. You can then find
multiple instances of either P.O. # or Invoice # by writing a Find
Duplicates query (use the wizard, it's easier that way).

Then do a dlookup on the DISTINCT numbers your query finds. This
should get you all the P.O. and Invoice numbers that have multiple
records associated with them.

Not an easy project for a n00b, you may want to write a process map and
just do one step at a time.
sara wrote:

I have been working this idea for a little over a week now and am not
convinced I have a good solution.

I have a little system that tracks Purchase Orders (POKey autonum,
PONum are both ont tblPOData, along with all the detail fields).

Recently the users asked if we could track Billing Information. "Of
course", I said.

Here's the thing:
We get an invoice that may be Freight for multiple POs, all on the same
invoice.

I need to have the users enter JUST the freight charge for the
individual PO, and attach it to that PO, so I can add up all the Actual
Freight charges to have TOTAL Actual Freight.

The users call the One Freight Bill, Multiple POs - Freigh FAK (Freight
of all Kinds).

One PO CAN also have freight on multiple FAK Invoices.

I have a button on the PO Update form that says "Freight". They press
the button and they can then enter all the freight for that PO. Works
fine.

They want, however, to "Identify" if the freight amount they're
entering is part of an FAK invoice (maybe I should use Invoice Type?)

They also want to enter the FAK Invoice Number, Freight Company (I'd
store the key), InvoiceDate and InvliceTotalAmt, so I can run reports
and say "Invoice 402 was for $1005, but you entered total freight on
POs for invoice 402 of $1010. Check your entries and correct" - or
whatever.

Here's what I have. I can't seem to figure out how to get the data and
relationships right, nor do I know how to have them enter the FAK info.
I thought if they choose "FAK" as invlice Type, show a popup that
would require them to enter the FAK invoice info? I'm lost.

tblFreightBill
freightBillKey - autonum
POKey - num
InvoiceNum - num
FreightCokey - num
BillingDate - date
FAKKey - number (I was thinking to use this to link to the other table?
Examine it - if 0, not an FAK?

tblFAK
FAKKey - autonum
InvoiceNum - num (would have to be the same as on the PO screen)
FreighCoKey - num (would have to be the same as on the PO screen for
the FAK portion - the PO can also have freight charged from another
company as well)
InvoiceAmt - currency
InvoiceDate - date (would have to be the same as on the PO screen)
Thanks - I appreciate any help. This is much more sophisticated than
anything I've done in my 6 months or so writing Access apps.

Sara