Queries, Look-up, and Expressions

[June7]

I won't be able to look at your project for 2 weeks (vacation). Make another post if you still need help then.

[June7]

Looks like an improvement and workable.

Should not need to save the total deposit amount in Deposits table. This amount would be calculated with aggregate query that joins Deposits and EmployeePayments tables. This is a principle concept of relational database - enter raw data and calculate summary data.

[Atlascycle]

June7,

do you have any advice for combining the employee payments and charges table to do a balance for each individual account?

Thanks again for your help.

Jason

[June7]

Try this approach:

Build two aggregate queries, one each for the Payments and Charges, group by Wearer.

Build another query that joins the two aggregate queries on the Wearer fields. Calculate difference of the sums to get balance.

I notice there are some credit records in the Invoices table. My comment about not saving Deposit amount in the Deposits table also applies to the Invoice amount in the Invoices table. However, the credit data does complicate this. Think I would make the credit a negative entry in the EmployeeCharges table.

[Atlascycle]

This is were I am at so Far, I do not think that i have the aggregate query correct.

Any Advice would be greatly appreciated.

Jason

[June7]

Your attempts are qryEmCharges and qryEmPayments? Those are not aggregate queries. Open in design view, click the Totals button (looks like sigma) from ribbon. Remove the filter criteria. Remove Transaction and ReceiptNum (you have Receipt misspelled as Reciept). Set GROUP BY under Wearer and SUM under PaymentAmount/Charges fields.

Then qryBalance will be a join of those two aggregate queries. Need the join link. It should be like 'show all records from qryEmCharges and only those from qryEmPayments that are equal'. This is aggregate data so transaction details will not be available.

Does that now show the result you want? There are several records in EmployeeCharges with no Wearer ID. Does invoice number 637-73625520 have an extra digit? Invoice number 637-7128849 has no data.

[Atlascycle]

Your attempts are qryEmCharges and qryEmPayments? Those are not aggregate queries. Open in design view, click the Totals button (looks like sigma) from ribbon. Remove the filter criteria. Remove Transaction and ReceiptNum (you have Receipt misspelled as Reciept). Set GROUP BY under Wearer and SUM under PaymentAmount/Charges fields.

Then qryBalance will be a join of those two aggregate queries. Need the join link. This is aggregate data so transaction details will not be available.

Does that now show the result you want? There are several records in EmployeeCharges with no Wearer ID I have Fixed This. Does invoice number 637-73625520 have an extra digit? Yes it did in fact, Good Eye. Invoice number 637-7128849 has no data. I had a Blank record, I have since Deleted the Blank record.

Thanks Again for your Help.

jason

[Atlascycle]

Originally Posted by June7
Your attempts are qryEmCharges and qryEmPayments? Those are not aggregate queries. Open in design view, click the Totals button (looks like sigma) from ribbon. Remove the filter criteria. Remove Transaction and ReceiptNum (you have Receipt misspelled as Reciept). Set GROUP BY under Wearer and SUM under PaymentAmount/Charges fields.

Then qryBalance will be a join of those two aggregate queries. Need the join link. This is aggregate data so transaction details will not be available.

Does that now show the result you want?

I have attached the current State of the Database, By making the changes as suggested I am able to view the total Charges and Total payments and calculate a balance. This will work for now.

I think that we might possible be at a point in the "Repair" of the database that we might possibly be able to return to the original question of the thread.

I have a Table for the Wearer, a Table for the Items to be rented and a junction table to assign multiple items to a Wearer.

Right now I have a Field in the Wearer table set-up fee, (I will change the name of this field it is actually service charge) I need to calculate the total of the items that a wearer rents, add the set-up fee(service charge) and then figure the total with 5.5% tax. To Do the calculation would it be easier if I had the Service Charge as a record in the Items Table and then used the Items_rented Table to link the Service Fee to the Wearer?