MS Access 2016, Same Fields But Values are different?

[dtoxic]

I am trying to make a small database in MS Access for a Gas Station, where I need to store data for every Single Fuel Dispenser's Current and Previous Readings. Which will then subtract Previous Reading from Current Reading and Calculate the Sales on a Given Rate. There are 4 Fuel Dispensers Installed on the Gas Station and every dispenser has 2 nozzles. I have Created 1 table for each Nozzle. Now I have a total of 8 tables with these fields (ID, Date, Rate, Current Reading, Previous Reading, Total Sale, Credit Sale, Cash Sale, Total Amount, Discount and Description (Images are Attached for Reference)).

I also want to make Forms so that the User can enter the data from the Forms for each Nozzle. Can someone please help me with this? I have been trying it for the last four days and I am still stuck and cannot go further.

NOTE: For each nozzle the readings will be Different.

[andy49]

This seems to be set up as a spreadsheet rather than a database


Sent from my iPhone using Tapatalk

[dtoxic]

This seems to be set up as a spreadsheet rather than a database

Exactly brother this is more like a spreadsheet rather than a database. Bro is there a way to make this like a Database.

[andy49]

Table1 readings


Table2 pumps

Might be a start


Sent from my iPhone using Tapatalk

[dtoxic]

Table1 readings


Table2 pumps

Might be a start

I will give it a try and then let you know about it. Thanks for your Suggestion andy49.

[dtoxic]

I have now Created two Tables, 1 for Pumps and the Second for Readings (images attached). Now should I link these two tables and proceed with the Queries, Forms and Reports?

[andy49]

You're database won't be normalised. I was thinking more of

tblreadings
ReadingID
PumpID
Readingdate
PumpRate



Sent from my iPhone using Tapatalk

[dtoxic]

You're database won't be normalised. I was thinking more of

tblreadings
ReadingID
PumpID
Readingdate
PumpRate

Oh I see, brother Andy, can you help me with these two tables please. I mean what fields I should have in Pumps Table and what fields in Readings Table. And a little guidance with the Relationships of tables as well. As you can see what I need. A little help from an expert like will be highly appreciable. Thanks.

[andy49]

You don't need to record before and end readings as they'd be the same. Just the reading at a particular date/time for each pump.

Calculate things such as used totalsale can be calculated from the data which is stored. Not sure about cash/credit sales etc. Need more info


Sent from my iPhone using Tapatalk

[andy49]

Pm sent


Sent from my iPhone using Tapatalk

[dtoxic]

You don't need to record before and end readings as they'd be the same. Just the reading at a particular date/time for each pump.

Calculate things such as used totalsale can be calculated from the data which is stored. Not sure about cash/credit sales etc. Need more info

Sure Brother, here are the Details:
1. The readings will be taken from the Pumps on Particular time. (for example 2 or more readings can be taken on the same date as the Rate changes frequently)
2. Current Readings – Previous Readings = Total Sale
3. Credit sale = Total sale – Number of Liters sold on Credit
4. Cash Sale = Total Sale – Credit Sale.
5. Total Amount = Cash Sale * Rate

[andy49]

How would the "number of litres sold on credit" be arrived at. In very simple language. Leave out the work related terms.


Sent from my iPhone using Tapatalk

[dtoxic]

Let me elaborate further bro. Suppose we have 2 Fuel Types we are selling. Diesel and Petrol. We have some regular customers who take fuel on Credit, means they pay us later. That's what Credit sale is.

[andy49]

Ok would you like to record that on the database and if so in what detail?


Sent from my iPhone using Tapatalk

[dtoxic]

Yes Brother, I want to record the credit sale on the DB as well.

Let me explain it with an example. Suppose this is our Yesterday's reading 123450, the Unit rate was $ 50.00
Now Today's rate is $52.00 and the Reading is 123555.
Yesterday's Reading = 123450 and the Rate was $50.00
Today's Reading = 123555 and the rate is $52.00

123555-123450 = 105 Liters. A regular customer took 5 Liters. 105 - 5 = 100

5 Liters on Credit. and 100 Liters on Cash.

100 Liters * Rate i.e. $52.00 = $5200.00

I hope this example will help you now that What I need for my DB. Thanks Bro